﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
/// <summary>
/// Summary description for Controllers
/// </summary>
public class Controllers
{
    DataProvider provider;

    public Controllers()
    {
        provider = new DataProvider();
    }

    public string HelloWorld()
    {
        return "Hello World";
    }
    /* @ Hàm DangNhap duoc dung de xac nhan user voi pass do ton tai trong he thong hay chua
     * @ username : username : string .. user name nhap vao
     * @ password : string  .pass cua user
     * return true neu thanh cong nguoc lai return false
     * */
    public bool DangNhap(string Username, string Password)
    {
        DataTable dt = new DataTable();
        string sql = "select * from WS_DoanhNghiep where taiKhoanDangNhap = '" + Username + "' and matKhau = '" + Password + "'";
        dt = provider.ExecuteQuery(sql);
        if (dt.Rows.Count == 1)
        {
            HttpContext.Current.Session.Add("IsLogin", true);
            HttpContext.Current.Session.Add("Username", Username);
            return true;
        }
        return false;
    }
    /*Phương thức Thoat được dùng để xác nhan user do duoc thoat khoi he thong
     * khong co tham so truyen vao
     * khong co du lieu tro ve
     * */
    public void Thoat()
    {
        if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
        {
            HttpContext.Current.Session.RemoveAll();
        }
    }
    /* @phuong thuc timSanBayDi : dung de tim danh sach cac san bay ma hang hang khong nay co the bay duoc
     * @khong co tham so
     * @ket qua tra ve: List<SanBay> la danh sach cac san bay thoa man. neu khong co san bay nao thi tra ve null
     * 
     * */
    public List<SanBay> TimSanBayDi()
    {
        List<SanBay> list = new List<SanBay>();
        try
        {
            string sql = "select distinct ID_sanBayDi,tenSanBay,thanhPho,quocGia from ChangBay c , SanBay s where c.ID_sanBayDi = s.ID_sanBay ";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(sql);
            foreach (DataRow dr in dt.Rows)
            {
                SanBay s = new SanBay();
                s.MaSanBay = dr["ID_sanBayDi"].ToString();
                s.TenSanBay = dr["tenSanBay"].ToString();
                s.TenThanhPho = dr["thanhPho"].ToString();
                s.QuocGia = dr["quocGia"].ToString();
                list.Add(s);
            }
        }
        catch
        {
            list = null;
        }

        return list;
    }
    /*@phuong thuc TimSanBayDen : dung de tim danh sach cac san bay ma hang hang khong nay co the bay den
    * @khong co tham so
    * @ket qua tra ve: List<SanBay> la danh sach cac san bay den thoa man. neu khong co san bay nao thi tra ve null
    * 
    * */
    public List<SanBay> TimSanBayDen()
    {
        List<SanBay> list = new List<SanBay>();
        try
        {
            string sql = "select distinct ID_sanBayDen,tenSanBay,thanhPho,quocGia from ChangBay c , SanBay s where c.ID_sanBayDen = s.ID_sanBay ";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(sql);
            foreach (DataRow dr in dt.Rows)
            {
                SanBay s = new SanBay();
                s.MaSanBay = dr["ID_sanBayDen"].ToString();
                s.TenSanBay = dr["tenSanBay"].ToString();
                s.TenThanhPho = dr["thanhPho"].ToString();
                s.QuocGia = dr["quocGia"].ToString();
                list.Add(s);
            }
        }
        catch
        {
            list = null;
        }

        return list;
    }

    //Tim gio bay khi biet ID_chuyenbay
    //by DauTai
    public GioBay TimGioBay(string idChuyenBay)
    {
        GioBay gioBay = new GioBay();
        int a = 0;
        try
        {
            string sql = "select gioDi, gioDen from ChangBay_ChuyenBay where ID_chuyenBay = '" + idChuyenBay + "'";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(sql);
            
            foreach (DataRow dr in dt.Rows)
            {
                gioBay.GioDi = dr["gioDi"].ToString().Substring(0, 5);
                gioBay.GioDen = dr["gioDen"].ToString().Substring(0, 5);
                a++;
                break;
            }
        }
        catch
        {
        }
        if (a == 0)
        {
            return null;
        }
        return gioBay;
    }

    /* @phuong thuc TimLoaiVe : phuong thuc nay dung de tim cac danh sach cac loai ve ma hang hang khong nay ho tro
     * @khong co tham so truyen vao
     * @ket qua tra ve List<LoaiVe> là danh sach cac loai ve thoa man.
     *
     * */
    public List<LoaiVe> TimLoaiVe()
    {
        List<LoaiVe> listTypeOfTicket = new List<LoaiVe>();
        try
        {
            string sql = "select * from LoaiVe ";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(sql);
            foreach (DataRow dr in dt.Rows)
            {
                LoaiVe lv = new LoaiVe();
                lv.MaLoai = dr["ID_loai"].ToString();
                lv.TenLoai = dr["tenLoaiVe"].ToString();
                lv.DuocPhepHuy = bool.Parse(dr["duocPhepHuy"].ToString());
                lv.DuocPhepNangHang = bool.Parse(dr["duocPhepNangHang"].ToString());
                lv.DuocPhepDoiLichTrinh = bool.Parse(dr["duocPhepDoiLichTrinh"].ToString());
                lv.PhiHuyTruocNgayKhoiHanh = double.Parse(dr["phiHuyTruocNgayKhoiHanh"].ToString());
                lv.PhiHuySauNgayKhoiHanh = double.Parse(dr["phiHuySauNgayKhoiHanh"].ToString());
                lv.PhiNangHangSauNgayKhoiHanh = double.Parse(dr["phiNangHangSauNgayKhoiHanh"].ToString());
                lv.PhiNangHangTruocNgayKhoiHanh = double.Parse(dr["phiNangHangTruocNgayKhoiHanh"].ToString());
                lv.PhiDoiLichTrinhTruocNgayKhoiHanh = double.Parse(dr["phiDoiLichTrinhTruocNgayKhoiHanh"].ToString());
                lv.PhiDoiLichTrinhSauNgayKhoiHanh = double.Parse(dr["phiDoiLichTrinhSauNgayKhoiHanh"].ToString());
                lv.HeSoCongDam = double.Parse(dr["heSoCongDam"].ToString());
                lv.ThoiHanSuDung = int.Parse(dr["thoiHanSuDung"].ToString());
                lv.DonViTien = dr["donViTien"].ToString();
                listTypeOfTicket.Add(lv);
            }
        }
        catch
        {
            listTypeOfTicket = null;
        }
        return listTypeOfTicket;
    }
    /* @phuong thuc layDanhSachGioDiTrongNgay : duoc dung lay danh sach cac gio di trong ngay ma hang hang khong nay co chuyen bay
     * @tham so 
     *      1. diemXuatPhat: String day la noi ma may bay bat dau xuat phat, 
     *      2. diemDen : string day la noi may bay den,
     *      3. ngayDi : DateTime ngay ma may xuat phat
     * @ket qua tra ve:List<string>  la danh sach cac gio di trong ngay neu co.
     * */
    public List<string> LayDanhSachGioDiTrongNgay(string diemXuatPhat, string diemDen, DateTime ngayDi)
    {

        List<string> listTime = new List<string>();
        if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
        {
            if (diemXuatPhat == null || diemDen == null || ngayDi == null)
            {
                listTime = null;
                return listTime;
            }
            //if (ngayDi.Date < DateTime.Now.Date)
            //{
            //    listTime = null;
            //    return listTime;
            //}
            try
            {
                string ngayDi1 = String.Format("{0:yyyy-MM-dd}", ngayDi);


                //  string sql = "select distinct  convert(varchar,cb.thoiGianDi,108) from ChuyenBay cb, ChangBay c where   convert(varchar,cb.thoiGianDi,101) = '" + ngayDi1 + "'  and c.ID_sanBayDi = '" + diemXuatPhat + "' and c.ID_sanBayDen = '" + diemDen + "' ";
                string sql = "select distinct  convert(varchar,cbcb.gioDi,108)  from ChangBay_ChuyenBay cbcb, ChuyenBay cb ,ChangBay c where   cbcb.ngayDi = '" + ngayDi1 + "' and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay ";
                sql += " and  (c.ID_sanBayDi = '" + diemXuatPhat + "' and cbcb.changThu = 1 ) and '" + diemDen + "'  in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang ) ";
                DataTable dt = new DataTable();
                dt = provider.ExecuteQuery(sql);
                foreach (DataRow dr in dt.Rows)
                {
                    string time = string.Empty;
                    time = dr[0].ToString();
                    listTime.Add(time);
                }

            }

            catch
            {
                listTime = null;
            }
        }
        else
            listTime = null;
        return listTime;
    }


    public List<GioBay> LayDanhSachGioDiGioDenTrongNgay(string diemXuatPhat, string diemDen, DateTime ngayDi)
    {

        List<GioBay> listTime = new List<GioBay>();

        if (diemXuatPhat == null || diemDen == null || ngayDi == null)
        {
            listTime = null;
            return listTime;
        }
        try
        {
            string ngayDi1 = String.Format("{0:yyyy-MM-dd}", ngayDi);


            string sql = "select distinct  convert(varchar,cbcb.gioDi,108), convert(varchar,cbcb.gioDen,108)   from ChangBay_ChuyenBay cbcb, ChuyenBay cb ,ChangBay c where   cbcb.ngayDi = '" + ngayDi1 + "' and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay ";
            sql += " and  (c.ID_sanBayDi = '" + diemXuatPhat + "' and cbcb.changThu = 1 ) and '" + diemDen + "'  in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang ) ";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(sql);
            foreach (DataRow dr in dt.Rows)
            {
                GioBay time = new GioBay();
                time.GioDi = dr[0].ToString();
                time.GioDen = dr[1].ToString();
                listTime.Add(time);
            }

        }

        catch
        {
            listTime = null;
        }
        return listTime;
    }
    /* @phuong thuc: TimGiaVe tra cuu gia ve dua vao diem di , diem den , theo loai ve , ma ve do co phai la ve khu hoi hay khong
     * @tham so: 
     *    1. maLoaiVe : string la ma cua loai ve(1,2,3,4,5,6); 
     *    2. diemDi: string  ma san bay ma may bay xuat phat
     *    3. diemDen : string ma san bay den ;
     *    4. loaiChuyenBay: string Ve có dinh dang(Mot Chieu , Khu Hoi),
     *    5. ngayDi : datetime thoigian di,
     *    6. gioDi: string gio di trong ngay do
     * @ket qua tra ve: double neu co ve thi tra ve gia ve theo yeu cau con khong thi tra ve giaVe = -1(khong co chuyen bay thoa man)
     * */
    public double TimGiaVe1(string maLoaiVe, string diemDi, string diemDen, string loaiChuyenBay, DateTime ngayDi, String gioDi)
    {
        double giaVe = -1.0;
        if (maLoaiVe == null || diemDi == null || diemDen == null || loaiChuyenBay == null || ngayDi == null || gioDi == null)
            return giaVe;
        try
        {

            string ngayDi1 = String.Format("{0:yyyy-MM-dd}", ngayDi);
            if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
            {
                string sql = "select bgv.giaVe from BangGiaVe bgv,ChuyenBay cb,ChangBay_ChuyenBay cbcb,ChangBay c where bgv.ID_loaiVe = '" + maLoaiVe + "' and   ";
                sql += " bgv.ID_chuyenBay = cb.ID_chuyenBay and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay and (c.ID_sanBayDI = '" + diemDi + "'";
                sql += " and cbcb.changThu = 1 ) and  '" + diemDen + "' in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang ) ";
                sql += "and cbcb.ngayDi = '" + ngayDi1 + "'and cbcb.gioDi = '" + gioDi + "'";
                DataTable dt = new DataTable();
                dt = provider.ExecuteQuery(sql);
                foreach (DataRow dr in dt.Rows)
                {
                    giaVe = Double.Parse(dr[0].ToString()) * 1.1;
                }

                if (giaVe != -1.0 && loaiChuyenBay.Equals("Khu Hoi"))
                    giaVe *= 2;
            }
        }
        catch
        {
            giaVe = -1;
        }
        return giaVe;
    }

    /* @phuong thuc: TimGiaVe tra cuu gia ve dua vao maLoaiVe , maSanBayDi , maSanBayDen,loaiChuyenBay,thoiGianDi,thoiGianVe
   * @tham so: 
   *    1. maLoaiVe : string la ma cua loai ve(1,2,3); 
   *    2. maSanBayDi: string  ma san bay ma may bay xuat phat
   *    3. maSanBayDen : string ma san bay den ;
   *    4. loaiChuyenBay: string Ve có dinh dang(Mot Chieu , Khu Hoi),
   *    5. thoiGianDi : datetime thoigian di,
   *    6. thoiGianVe: datetime thoigian di,
   * @ket qua tra ve: double neu co ve thi tra ve gia ve theo yeu cau con khong thi tra ve giaVe = -1(khong co chuyen bay thoa man)
   * */
    public double TimGiaVe(string maLoaiVe, string maSanBayDi, string maSanBayDen, DateTime thoiGianDi, DateTime ThoiGianVe, string loaiChuyenBay)
    {

        double giaVe = -1.0;
        if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
        {
            if (maLoaiVe == null || maSanBayDi == null || maSanBayDen == null || loaiChuyenBay == null || thoiGianDi == null)
                return giaVe;

            try
            {

                string ngayDi1 = String.Format("{0:yyyy-MM-dd}", thoiGianDi);

                string sql = "select bgv.giaVe from BangGiaVe bgv,ChuyenBay cb,ChangBay_ChuyenBay cbcb,ChangBay c where bgv.ID_loaiVe = '" + maLoaiVe + "' and   ";
                sql += " bgv.ID_chuyenBay = cb.ID_chuyenBay and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay and (c.ID_sanBayDI = '" + maSanBayDi + "'";
                sql += " and cbcb.changThu = 1 ) and  '" + maSanBayDen + "' in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang ) ";
                sql += "and cbcb.ngayDi = '" + ngayDi1 + "'";
                DataTable dt = new DataTable();
                dt = provider.ExecuteQuery(sql);
                foreach (DataRow dr in dt.Rows)
                {
                    giaVe = Double.Parse(dr[0].ToString()) * 1.1;
                }

                string ID_chuyenBayVe = "";
                if (loaiChuyenBay.Equals("Khu Hoi") && ThoiGianVe != null)
                {
                    ID_chuyenBayVe = TimChuyenBay(ThoiGianVe, maSanBayDen, maSanBayDi, maLoaiVe, 1);
                    if (giaVe != -1.0 && ID_chuyenBayVe != null)
                    {
                        sql = "select bgv.giave from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and ID_loaiVe = " + int.Parse(maLoaiVe) + "";
                        dt = provider.ExecuteQuery(sql);

                        if (dt.Rows.Count > 0)
                        {
                            giaVe += Double.Parse(dt.Rows[0]["giaVe"].ToString()) * 1.1;
                        }

                    }
                    else
                        giaVe = -1;
                }


            }
            catch
            {
                giaVe = -1;
            }
        }
        return giaVe;
    }
    /*
     * @phuong thuc TimChuyenBay : tim chuyen bay thoa man voi ngayGioDi,DiemDi,DiemDen ,loaiVe,soCho cho truoc
     * @tham so 
     *    1. DateTime ngayGioDi : ngay Gio Di trong ngay
     *    2. String DiemDi" noi ma may bay xuat phat
     *    3. DiemDen : noi ma may bay den
     *    4. loaiVe : la loaiVe dat thuoc dang (1,2,3,4,5,6)
     *    5. soCho : so cho ngoi
     * @ ket qua tra ve : string
     *      ID_chuyenBay thoa man...neu khong co thi ID_chuyenBay = null;
     * */
    public string TimChuyenBay(DateTime ngayGioDi, string DiemDi, string DiemDen, string loaiVe, int soCho)
    {

        string ID_chuyenBay = null;
        try
        {
            string sql = "select cb.ID_chuyenBay from ChuyenBay cb,ChangBay_ChuyenBay cbcb, ChangBay c,BangGiaVe bgv where cbcb.ngayDi = '" + ngayGioDi + "'  and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cb.tinhTrang = 0 and";
            sql = sql + " cbcb.ID_changBay = c.ID_changBay and (c.ID_sanBayDI = '" + DiemDi + "' and cbcb.changThu = 1)  and cb.ID_chuyenBay = bgv.ID_chuyenBay and ID_loaiVe = " + loaiVe + " and soChoHienTai >= " + soCho + " ";
            sql += " and '" + DiemDen + "' in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang )";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(sql);
            foreach (DataRow dr in dt.Rows)
            {
                ID_chuyenBay = dr[0].ToString();
                break;
            }
        }
        catch
        {
            ID_chuyenBay = null;
        }
        return ID_chuyenBay;
    }
    /*
     * @phuong thuc YeuCauGiuCho : yeu cau giu cho cho cong ty muon dat cho truoc
     * @tham so
     *    1. soLuongChoNgoi : int so luong cho ngoi
     *    2. maLoaiVe : string la loaiVe dat thuoc dang (1,2,3).
     *    3. ngayGioDi : DateTime  ngay gio di
     *    4. ngayGioVe : DateTime ngay gio ve
     *    5. loaiChuyenBay : string   la ve thuoc dang (Mot Chieu, Khu Hoi)
     *    6. diemDi : string    ma san bay di
     *    7. diemDen : string   ma san bay den
     *@ ket qua tra ve
     *      string  ma duoc giu cho.
     *         
     * 
     * */
    public string YeuCauGiuCho(int soLuongNguoi, string maLoaiVe, DateTime ngayGioDi, DateTime ngayGioVe, string loaiChuyenBay, string diemDi, string diemDen)
    {
        string MaGiuCho = "";
        if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
        {
            if (soLuongNguoi == 0 || maLoaiVe == null || ngayGioDi == null || ngayGioVe == null || loaiChuyenBay == null || diemDi == null || diemDen == null)
            {

                return MaGiuCho;
            }
            //if(ngayGioDi.Date < DateTime.Now.Date )
            //{

            //   return MaGiuCho;
            // }
            try
            {

                int Count = 0;  // dem so cho da duoc dat voi ID_chuyenBay co duoc
                string ID_chuyenBayDi = TimChuyenBay(ngayGioDi, diemDi, diemDen, maLoaiVe, soLuongNguoi);
                string ID_chuyenBayVe = null;
                if (loaiChuyenBay.Equals("Khu Hoi")) // neu la ve khu hoi
                {
                    if (ngayGioVe.Date < ngayGioDi.Date || ngayGioVe < DateTime.Now.Date)
                    {
                        MaGiuCho = "";

                        return MaGiuCho;
                    }
                    ID_chuyenBayVe = TimChuyenBay(ngayGioVe, diemDen, diemDi, maLoaiVe, soLuongNguoi); // tim ID_ChuyenBayve khu hoi
                    if (ID_chuyenBayDi == null || ID_chuyenBayVe == null)
                        return MaGiuCho; // tra ve neu nhu khong co chuyen bay di hay ve thoa man
                }
                if (ID_chuyenBayDi != null)
                {
                    DataTable dt = new DataTable();
                    SqlTransaction trans;
                    provider.connection().Open(); // mo ket noi
                    trans = provider.connection().BeginTransaction(); // bat dau giao tac
                    SqlCommand command = new SqlCommand();
                    command.CommandType = CommandType.Text;
                    command.Connection = provider.connection();
                    command.Transaction = trans;
                    string strcount = "select count(*) from BangGiuCho where ID_chuyenBayDi = '" + ID_chuyenBayDi + "'";
                    command.CommandText = strcount;
                    dt = provider.ExecuteQuery(command);
                    DataRow dr = dt.Rows[0];
                    if (int.Parse(dr[0].ToString()) > 0)
                    {
                        Count = int.Parse(dr[0].ToString());
                    }
                    string ID_cho = "B" + ID_chuyenBayDi + Count.ToString();// phat sinh ma cho
                    MaGiuCho = ID_cho;
                    string sql = "insert into BangGiuCho values('" + ID_cho + "','" + ID_chuyenBayDi + "',null," + int.Parse(maLoaiVe) + ",'" + HttpContext.Current.Session["Username"] + "', '" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "'," + soLuongNguoi + ",0)";
                    command.CommandText = sql;
                    provider.excute(command);
                    string strsqlupdate = "update BangGiaVe set soChoHienTai = soChoHienTai - " + soLuongNguoi + " where ID_chuyenBay = '" + ID_chuyenBayDi + "' and ID_loaiVe = " + int.Parse(maLoaiVe) + "";
                    command.CommandText = strsqlupdate;
                    provider.excute(command);



                    if (loaiChuyenBay.Equals("Khu Hoi"))
                    {

                        if (ID_chuyenBayVe != null)
                        {

                            sql = "";
                            sql = "update BangGiuCho set ID_chuyenBayVe= '" + ID_chuyenBayVe + "' where ID_giuCho = '" + ID_cho + "'";
                            command.CommandText = sql;
                            provider.excute(command);
                            strsqlupdate = "update BangGiaVe set soChoHienTai = soChoHienTai - " + soLuongNguoi + " where ID_chuyenBay = '" + ID_chuyenBayVe + "' and ID_loaiVe = " + int.Parse(maLoaiVe) + "";
                            command.CommandText = strsqlupdate;
                            provider.excute(command); // update lai so cho con lai 

                        }
                    }
                    trans.Commit();
                    provider.closeConnection();
                }
            }

            catch
            {
                MaGiuCho = "";
                provider.closeConnection();
            }
        }
        else
            MaGiuCho = "";

        return MaGiuCho;
    }



    //,List<KhachHang> khachHang
    /*
     * @phuong thuc:  DatVeMayBay : dat ve may bay
     * @tham so
     *     
     *     2. maLoaiVe string : la loai Ve dat thuoc dang (1,2,3).
     *     3. ngayGioDi DateTime : ngay gio di
     *     4. ngayGioVe DateTime: ngay gio ve
     *     5. loaiChuyenBay string : ve thuoc khu hoi hay mot chieu co dinh dang (Mot Chieu, Khu Hoi)
     *     6. diemDi string : ma san bay di
     *     7. diemDen string : ma san bay den
     *     8. khachHang List<KhachHang> danh sach khach hang dat ve
     *     
     * @ ket qua tra ve
     *     HoaDonMayBay  : hoa don may bay trong do co: danh sach ve voi tung loai ve thuoc ve mot khach hang va danh sach chang bay lien quan
     * 
     * */
    public HoaDonMayBay DatVeMayBay(string maLoaiVe, DateTime ngayGioDi, DateTime ngayGioVe, string loaiChuyenBay, string diemDi, string diemDen, List<KhachHang> khachHang)
    {


        HoaDonMayBay hd = new HoaDonMayBay(); // hoa don 
        if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
        {
            try
            {

                List<VeMayBay> listVeMayBay = new List<VeMayBay>(); // danh sach ve

                string ID_chuyenBayDi = TimChuyenBay(ngayGioDi, diemDi, diemDen, maLoaiVe, khachHang.Count);// tim chuyen bay di
                string ID_chuyenBayVe = null;// chuyen bay ve
                // neu la ve khu hoi
                if (loaiChuyenBay.Equals("Khu Hoi"))
                {
                    if (ngayGioVe.Date < DateTime.Now.Date || ngayGioVe.Date < ngayGioDi.Date)
                    {
                        hd = null;
                        return hd;
                    }
                    ID_chuyenBayVe = TimChuyenBay(ngayGioVe, diemDen, diemDi, maLoaiVe, khachHang.Count);
                    if (ID_chuyenBayDi == null || ID_chuyenBayVe == null)
                        return hd;
                }
                if (ngayGioDi.Date < DateTime.Now.Date)
                {
                    hd = null;
                    return hd;
                }
                if (khachHang.Count == 0 || maLoaiVe == null || ngayGioDi == null || loaiChuyenBay == null || diemDi == null || diemDen == null)
                    return hd;
                else
                {
                    SqlTransaction trans;
                    provider.connection().Open(); // mo ket noi
                    trans = provider.connection().BeginTransaction(); // bat dau giao tac
                    SqlCommand command = new SqlCommand();
                    command.CommandType = CommandType.Text;
                    command.Connection = provider.connection();
                    command.Transaction = trans;
                    if (ID_chuyenBayDi != null) // neu la chuyen bay di
                    {
                        string strsql = "";
                        DataTable dt = new DataTable();
                        int Count = 0; // den xem co bao nhieu dong thoa man voi ID_chuyenBayDi
                        string strcount = "select count(*) from HoaDon where ID_chuyenBayDi = '" + ID_chuyenBayDi + "'";
                        command.CommandText = strcount;
                        // dt = provider.ExecuteQuery(strcount);
                        dt = provider.ExecuteQuery(command); // thuc thi cau lenh
                        DataRow dr = dt.Rows[0];
                        if (int.Parse(dr[0].ToString()) > 0)
                        {
                            Count = int.Parse(dr[0].ToString()); // ket qua
                        }

                        // xem gia ve
                        strsql = "select bgv.giaVe from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = " + maLoaiVe + " ";
                        dt = null;
                        double giave = 0.0;
                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (int.Parse(dt.Rows.Count.ToString()) > 0)
                            giave = double.Parse(dt.Rows[0]["giaVe"].ToString());
                        // tim phan tram gia ve tuy theo doi tuong
                        strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 1";
                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        double phanTramGiaVeNguoiLon = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());

                        strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 2";

                        //  dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        double phanTramGiaVeTremLonHon2NhoHon12 = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());

                        strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 3";

                        // dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        double phanTramGiaVeTrEmNhoHon2 = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());
                        int soVeNguoiLon = 0;  // tim so ve nguoi lon
                        int soVeNguoiTreEmLonHon2NhoHon12 = 0; // tim so ve tre em lon hon 2 tuoi nhung nho hon 12 tuoi
                        int soVeNguoiTreEmNhoHon2 = 0;    // tim so ve tre em nho hon 2 tuoi
                        for (int i = 0; i < khachHang.Count(); i++)
                            if (khachHang[i].LoaiDoiTuong == 1)
                            {
                                soVeNguoiLon++;
                            }
                            else
                                if (khachHang[i].LoaiDoiTuong == 2)
                                    soVeNguoiTreEmLonHon2NhoHon12++;
                                else
                                    soVeNguoiTreEmNhoHon2++;
                        // tinh tien ma cong ty dat cho phai tra
                        double tongTien = (soVeNguoiLon * phanTramGiaVeNguoiLon * giave + soVeNguoiTreEmLonHon2NhoHon12 * phanTramGiaVeTremLonHon2NhoHon12 * giave + soVeNguoiTreEmNhoHon2 * phanTramGiaVeTrEmNhoHon2 * giave) * 1.1;

                        string ngayDi1 = String.Format("{0:dd-MM-yyyy}", DateTime.Now);

                        if (loaiChuyenBay.Equals("Khu Hoi") && ID_chuyenBayVe != null) // neu that su ve nay la ve khu hoi va co chuyen bay ve
                        {

                            strsql = "select bgv.giaVe from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = '" + maLoaiVe + "' ";
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (int.Parse(dt.Rows.Count.ToString()) > 0)
                                giave = double.Parse(dt.Rows[0]["giaVe"].ToString());
                            tongTien += (soVeNguoiLon * phanTramGiaVeNguoiLon * giave + soVeNguoiTreEmLonHon2NhoHon12 * phanTramGiaVeTremLonHon2NhoHon12 * giave + soVeNguoiTreEmNhoHon2 * phanTramGiaVeTrEmNhoHon2 * giave) * 1.1;

                        }
                        string ID_hoaDon = "HD" + ID_chuyenBayDi + Count.ToString(); // tim ID hoa don
                        string ngayDi = String.Format("{0:yyyy-MM-dd}", DateTime.Now); // dinh dang chuoi ngay
                        strsql = "insert into HoaDon values('" + ID_hoaDon + "','" + ID_chuyenBayDi + "',null,'" + ngayDi + "'," + khachHang.Count + ",'" + HttpContext.Current.Session["Username"] + "'," + tongTien + ",0,0) ";
                        //  provider.excute(strsql);
                        command.CommandText = strsql;
                        provider.excute(command);// them hoa don vao he thong
                        // tao hoa don
                        hd.ID_hoaDon1 = ID_hoaDon;
                        hd.LoaiVe = maLoaiVe;
                        hd.LoaiChuyenBay = loaiChuyenBay;
                        hd.NgayDat = ngayDi1;
                        hd.TaiKhoan = HttpContext.Current.Session["Username"].ToString();
                        hd.TongTien = tongTien;

                        // Chuyen Bay Di
                        string soHieuMayBayDi = ""; // so hieu chuyen bay di
                        string soHieuMayBayVe = ""; // so hieu chuyen bay ve
                        strsql = "select mb.soHieuMayBay from ChuyenBay cb ,MayBay mb where cb.ID_chuyenBay = '" + ID_chuyenBayDi + "' and cb.ID_mayBay = mb.ID_mayBay";

                        //  dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (int.Parse(dt.Rows.Count.ToString()) > 0)
                        {
                            soHieuMayBayDi = dt.Rows[0]["soHieuMayBay"].ToString();

                        }
                        // Chuyen Bay Ve
                        if (loaiChuyenBay.Equals("Khu Hoi") && ID_chuyenBayVe != null)
                        {

                            strsql = "select mb.soHieuMayBay from ChuyenBay cb ,MayBay mb where cb.ID_chuyenBay = '" + ID_chuyenBayVe + "' and cb.ID_mayBay = mb.ID_mayBay";
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (int.Parse(dt.Rows.Count.ToString()) > 0)
                            {
                                soHieuMayBayVe = dt.Rows[0]["soHieuMayBay"].ToString();
                            }
                        }
                        // tinh so cho cho tung loai ve
                        int soGheLoaiVe1 = 0;
                        int soGheLoaiVe2 = 0;
                        int soGheLoaiVe3 = 0;
                        //   int soGheLoaiVe4 = 0;
                        //  int soGheLoaiVe5 = 0;
                        // int soGheLoaiVe6 = 0;
                        int soGheLoaiVe1_Ve = 0;
                        int soGheLoaiVe2_Ve = 0;
                        int soGheLoaiVe3_Ve = 0;
                        //  int soGheLoaiVe4_Ve = 0;
                        //  int soGheLoaiVe5_Ve = 0;
                        // int soGheLoaiVe6_Ve = 0;

                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = 1 ";

                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (dt.Rows.Count > 0)
                        {
                            soGheLoaiVe1 = int.Parse(dt.Rows[0]["soCho"].ToString());
                        }
                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = 2 ";

                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (dt.Rows.Count > 0)
                        {
                            soGheLoaiVe2 = int.Parse(dt.Rows[0]["soCho"].ToString());
                        }
                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = 3 ";

                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (dt.Rows.Count > 0)
                        {
                            soGheLoaiVe3 = int.Parse(dt.Rows[0]["soCho"].ToString());
                        }

                        //// tinh so ghe doi voi ve khu hoi

                        if (loaiChuyenBay.Equals("Khu Hoi") && ID_chuyenBayVe != null)
                        {
                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = 1 ";


                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (dt.Rows.Count > 0)
                            {
                                soGheLoaiVe1_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                            }
                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = 2 ";


                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (dt.Rows.Count > 0)
                            {
                                soGheLoaiVe2_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                            }
                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = 3 ";


                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (dt.Rows.Count > 0)
                            {
                                soGheLoaiVe3_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                            }

                        }
                        for (int i = 0; i < khachHang.Count; i++)
                        {


                            strsql = "";

                            string ID_KH = "";
                            strsql = "select ID_khach from Khach where soThe = '" + khachHang[i].SoThe + "'";
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (dt.Rows.Count > 0)
                            {
                                if (dt.Rows[0]["ID_khach"].ToString().Length > 0)
                                    ID_KH = dt.Rows[0]["ID_khach"].ToString();

                            }
                            else
                                if (ID_KH.Length < 1)
                                {
                                    ID_KH = "KH" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                    strsql = "insert into Khach values('" + ID_KH + "',null,'" + khachHang[i].HoTenKhachHang + "','" + khachHang[i].GioiTinh + "','" + khachHang[i].NgaySinh + "','" + khachHang[i].Email + "','" + khachHang[i].SoDienThoaiDiDong + "','" + khachHang[i].SoDienThoaiBan + "' ";
                                    strsql += "  ,'" + khachHang[i].LoaiGiayTo + "','" + khachHang[i].SoThe + "','" + khachHang[i].NuocPhatHanh + "','" + khachHang[i].QuocTich + "','" + khachHang[i].NgayHetHan + "'," + khachHang[i].LoaiDoiTuong + ",0)";

                                    command.CommandText = strsql;
                                    provider.excute(command);
                                }
                            // Chuyen Bay Di
                            strsql = "";
                            strsql = "select bgv.soChoHienTai from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = '" + maLoaiVe + "'";
                            dt = null;
                            // dt = provider.ExecuteQuery(strsql);
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            int viTri = int.Parse(dt.Rows[0]["SoChoHienTai"].ToString());
                            if (int.Parse(maLoaiVe) == 1)
                            {
                                viTri = soGheLoaiVe1 - viTri + 1;
                            }
                            else
                                if (int.Parse(maLoaiVe) == 2)
                                {
                                    viTri = soGheLoaiVe1 + soGheLoaiVe2 - viTri + 1;
                                }
                                else
                                    if (int.Parse(maLoaiVe) == 3)
                                    {
                                        viTri = soGheLoaiVe1 + soGheLoaiVe2 + soGheLoaiVe3 - viTri + 1;
                                    }

                            string ID_ve = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                            strsql = "insert into VeMayBay values('" + ID_ve + "','" + ID_KH + "','" + maLoaiVe + "','" + ID_chuyenBayDi + "','" + viTri.ToString() + "','" + ngayDi + "',0,0)";
                            // provider.excute(strsql);
                            command.CommandText = strsql;
                            provider.excute(command);
                            strsql = "";
                            strsql = "insert into ChiTietHoaDon values('" + ID_hoaDon + "','" + ID_ve + "')"; // Chi Tiet Hoa Don
                            //   provider.excute(strsql);
                            command.CommandText = strsql;
                            provider.excute(command);
                            strsql = "update BangGiaVe set soChoHienTai = soChoHienTai - 1 where ID_chuyenBay = '" + ID_chuyenBayDi + "' and ID_loaiVe = " + maLoaiVe + "";
                            // provider.excute(strsql);
                            command.CommandText = strsql;
                            provider.excute(command);
                            // add vào Vé
                            VeMayBay vmb = new VeMayBay();
                            vmb.SoHieuMayBay = soHieuMayBayDi;
                            vmb.ID_ve1 = ID_ve;
                            vmb.TenKhachHang = khachHang[i].HoTenKhachHang;

                            if (khachHang[i].LoaiDoiTuong == 1)
                                vmb.GiaVe = giave * phanTramGiaVeNguoiLon;
                            else
                                if (khachHang[i].LoaiDoiTuong == 2)
                                    vmb.GiaVe = giave * phanTramGiaVeTremLonHon2NhoHon12;
                                else
                                    vmb.GiaVe = vmb.GiaVe = giave * phanTramGiaVeTrEmNhoHon2;
                            vmb.Thue = vmb.GiaVe * 0.1;
                            vmb.TongTien = vmb.GiaVe + vmb.Thue;
                            strsql = " select sb.thanhPho as thanhPhoDi , (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as ngayKhoiHanh ,";
                            strsql += " UPPER(SUBSTRING(DATENAME(DW,cbcb.ngayDi),0,3)) as ngayTrongTuanKhoiHanh ,(UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDi,108),0,3)) + UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDi,108),4,2))) as gioKhoiHanh ,";
                            strsql += " (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))+ '/' + UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as thoiHanVe ,";
                            strsql += " sb1.thanhPho as thanhPhoDen, (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as ngayDen, ";
                            strsql += " UPPER(SUBSTRING(DATENAME(DW,cbcb.ngayDen),0,3)) as ngayTrongTuanDen,(UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDen,108),0,3)) + UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDen,108),4,2))) as gioDen ,";
                            strsql += "cbcb.tongGioBay from SanBay sb,SanBay sb1,ChangBay_ChuyenBay cbcb,ChangBay cb where cbcb.ID_chuyenBay = '" + ID_chuyenBayDi + "'  and cbcb.ID_changBay = cb.ID_changBay and cb.ID_sanBayDi = sb.ID_sanBay and cb.ID_sanBayDen = sb1.ID_sanBay";
                            dt = null;
                            // dt = provider.ExecuteQuery(strsql);
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            List<ChiTietChangBayChuyenBay> listChiTietChuyenBay = new List<ChiTietChangBayChuyenBay>();
                            foreach (DataRow dr1 in dt.Rows)
                            {
                                ChiTietChangBayChuyenBay chitietCB = new ChiTietChangBayChuyenBay();
                                chitietCB.TenThanhPhoDi = dr1["thanhPhoDi"].ToString();
                                chitietCB.NgayKhoiHanh = dr1["ngayKhoiHanh"].ToString();
                                chitietCB.NgayTrongTuanKhoiHanh = dr1["gioKhoiHanh"].ToString();
                                chitietCB.GioKhoiHanh = dr1["ngayTrongTuanKhoiHanh"].ToString();
                                chitietCB.ThoiHanVe = dr1["thoiHanVe"].ToString();
                                chitietCB.ViTri = viTri.ToString();
                                chitietCB.TenThanhPhoDen = dr1["thanhPhoDen"].ToString();
                                chitietCB.NgayDen = dr1["ngayDen"].ToString();
                                chitietCB.NgayTrongTuanDen = dr1["ngayTrongTuanDen"].ToString();
                                chitietCB.GioKhoiHanh = dr1["gioDen"].ToString();
                                chitietCB.TongThoiGianBay = dr1["tongGioBay"].ToString();
                                listChiTietChuyenBay.Add(chitietCB); // them chi tiet cac chang bay vao danh sach chi tiet chuyen bay

                            }

                            vmb.ListChiTiet = listChiTietChuyenBay; // gan danh sach chi tiet bay vào ve may bay

                            listVeMayBay.Add(vmb);// them ve may bay vao danh sach ve

                            // Chuyen Bay Ve
                            if (loaiChuyenBay.Equals("Khu Hoi") && ID_chuyenBayVe != null)
                            {
                                strsql = "select bgv.soChoHienTai from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = '" + maLoaiVe + "'";
                                dt = null;
                                // dt = provider.ExecuteQuery(strsql);
                                command.CommandText = strsql;
                                dt = provider.ExecuteQuery(command);
                                int viTri_Ve = int.Parse(dt.Rows[0]["SoChoHienTai"].ToString());
                                if (int.Parse(maLoaiVe) == 1)
                                {
                                    viTri_Ve = soGheLoaiVe1_Ve - viTri_Ve + 1;
                                }
                                else
                                    if (int.Parse(maLoaiVe) == 2)
                                    {
                                        viTri_Ve = soGheLoaiVe1_Ve + soGheLoaiVe2_Ve - viTri_Ve + 1;
                                    }
                                    else
                                        if (int.Parse(maLoaiVe) == 3)
                                        {
                                            viTri_Ve = soGheLoaiVe1_Ve + soGheLoaiVe2_Ve + soGheLoaiVe3_Ve - viTri_Ve + 1;
                                        }

                                string ID_ve_ChuyenVe = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                strsql = "insert into VeMayBay values('" + ID_ve_ChuyenVe + "','" + ID_KH + "','" + maLoaiVe + "','" + ID_chuyenBayVe + "','" + viTri.ToString() + "','" + ngayDi + "',0,0)";
                                // provider.excute(strsql);
                                command.CommandText = strsql;
                                provider.excute(command);
                                //update HoaDon
                                strsql = "update HoaDon set ID_chuyenBayVe = '" + ID_chuyenBayVe + "' where ID_hoaDon = '" + ID_hoaDon + "'";
                                command.CommandText = strsql;
                                provider.excute(command);
                                // insert into ChiTietHoaDon
                                strsql = "";
                                strsql = "insert into ChiTietHoaDon values('" + ID_hoaDon + "','" + ID_ve_ChuyenVe + "')";
                                command.CommandText = strsql;
                                provider.excute(command);
                                // update BangGiaVe
                                strsql = "update BangGiaVe set soChoHienTai = soChoHienTai - 1 where ID_chuyenBay = '" + ID_chuyenBayVe + "' and ID_loaiVe = " + maLoaiVe + "";
                                command.CommandText = strsql;
                                provider.excute(command);
                                // add vào Vé
                                vmb = new VeMayBay();
                                vmb.SoHieuMayBay = soHieuMayBayVe;
                                vmb.ID_ve1 = ID_ve_ChuyenVe;
                                vmb.TenKhachHang = khachHang[i].HoTenKhachHang;

                                if (khachHang[i].LoaiDoiTuong == 1)
                                    vmb.GiaVe = giave * phanTramGiaVeNguoiLon;
                                else
                                    if (khachHang[i].LoaiDoiTuong == 2)
                                        vmb.GiaVe = giave * phanTramGiaVeTremLonHon2NhoHon12;
                                    else
                                        vmb.GiaVe = vmb.GiaVe = giave * phanTramGiaVeTrEmNhoHon2;
                                vmb.Thue = vmb.GiaVe * 0.1;// thue 10%
                                vmb.TongTien = vmb.GiaVe + vmb.Thue; // tong tien
                                strsql = " select sb.thanhPho as thanhPhoDi , (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as ngayKhoiHanh ,";
                                strsql += " UPPER(SUBSTRING(DATENAME(DW,cbcb.ngayDi),0,3)) as ngayTrongTuanKhoiHanh ,(UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDi,108),0,3)) + UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDi,108),4,2))) as gioKhoiHanh ,";
                                strsql += " (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))+ '/' + UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as thoiHanVe ,";
                                strsql += " sb1.thanhPho as thanhPhoDen, (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as ngayDen, ";
                                strsql += " UPPER(SUBSTRING(DATENAME(DW,cbcb.ngayDen),0,3)) as ngayTrongTuanDen,(UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDen,108),0,3)) + UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDen,108),4,2))) as gioDen ,";
                                strsql += "cbcb.tongGioBay from SanBay sb,SanBay sb1,ChangBay_ChuyenBay cbcb,ChangBay cb where cbcb.ID_chuyenBay = '" + ID_chuyenBayVe + "'  and cbcb.ID_changBay = cb.ID_changBay and cb.ID_sanBayDi = sb.ID_sanBay and cb.ID_sanBayDen = sb1.ID_sanBay";
                                dt = null;
                                // dt = provider.ExecuteQuery(strsql);
                                command.CommandText = strsql;
                                dt = provider.ExecuteQuery(command);

                                listChiTietChuyenBay = new List<ChiTietChangBayChuyenBay>();// khai bao danh sach chi tiet chuyen bay
                                foreach (DataRow dr1 in dt.Rows)
                                {
                                    ChiTietChangBayChuyenBay chitietCB = new ChiTietChangBayChuyenBay();// khai bao chi tiet chang bay
                                    chitietCB.TenThanhPhoDi = dr1["thanhPhoDi"].ToString();
                                    chitietCB.NgayKhoiHanh = dr1["ngayKhoiHanh"].ToString();
                                    chitietCB.NgayTrongTuanKhoiHanh = dr1["gioKhoiHanh"].ToString();
                                    chitietCB.GioKhoiHanh = dr1["ngayTrongTuanKhoiHanh"].ToString();
                                    chitietCB.ThoiHanVe = dr1["thoiHanVe"].ToString();
                                    chitietCB.ViTri = viTri.ToString();
                                    chitietCB.TenThanhPhoDen = dr1["thanhPhoDen"].ToString();
                                    chitietCB.NgayDen = dr1["ngayDen"].ToString();
                                    chitietCB.NgayTrongTuanDen = dr1["ngayTrongTuanDen"].ToString();
                                    chitietCB.GioKhoiHanh = dr1["gioDen"].ToString();
                                    chitietCB.TongThoiGianBay = dr1["tongGioBay"].ToString();
                                    listChiTietChuyenBay.Add(chitietCB);// them chang bay vao danh sach chi tiet chuyen bay

                                }

                                vmb.ListChiTiet = listChiTietChuyenBay;  // gan danh sach chi tiet

                                listVeMayBay.Add(vmb); // them ve may bay vao danh sach ve may bay

                            }

                        }

                        hd.ListVe = listVeMayBay; // gan danh sach ve vao hoa don
                    }
                    trans.Commit();// cam ket giao tac thanh cong
                    provider.closeConnection();// dong ket noi
                }

            }
            catch
            {
                provider.closeConnection();// dong ket noi khi gap loi
                hd = null;

            }
        }
        else
            hd = null;
        return hd;

    }
    /*
     * Test
     * */
    public HoaDonMayBay DatVeMayBay_Test(string maLoaiVe, DateTime ngayGioDi, DateTime ngayGioVe, string loaiChuyenBay, string diemDi, string diemDen)
    {


        HoaDonMayBay hd = new HoaDonMayBay(); // hoa don 
        if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
        {
            try
            {

                List<VeMayBay> listVeMayBay = new List<VeMayBay>(); // danh sach ve
                List<KhachHang> khachHang = new List<KhachHang>();
                KhachHang kh = new KhachHang();
                kh.HoTenKhachHang = "Vo Thanh Tuan";
                kh.GioiTinh = false;
                kh.NgaySinh = DateTime.Now;
                kh.LoaiDoiTuong = 1;
                kh.LoaiGiayTo = "CMND";
                kh.NgayHetHan = DateTime.Now;
                kh.NuocPhatHanh = "VN";
                kh.QuocTich = "VN";
                kh.SoThe = "217154562";
                kh.SoDienThoaiDiDong = "121212121";
                kh.SoDienThoaiBan = "";
                khachHang.Add(kh);
                KhachHang kh1 = new KhachHang();
                kh1.HoTenKhachHang = "NVA";
                kh1.GioiTinh = true;
                kh1.NgaySinh = DateTime.Now;
                kh1.LoaiDoiTuong = 1;
                kh1.LoaiGiayTo = "CMND";
                kh1.NgayHetHan = DateTime.Now;
                kh1.NuocPhatHanh = "VN";
                kh1.QuocTich = "VN";
                kh1.SoThe = "215154564";
                kh1.SoDienThoaiDiDong = "121212121";
                kh1.SoDienThoaiBan = "";
                khachHang.Add(kh1);

                string ID_chuyenBayDi = TimChuyenBay(ngayGioDi, diemDi, diemDen, maLoaiVe, khachHang.Count);// tim chuyen bay di
                string ID_chuyenBayVe = null;// chuyen bay ve
                // neu la ve khu hoi
                if (loaiChuyenBay.Equals("Khu Hoi"))
                {
                    if (ngayGioVe.Date < DateTime.Now.Date || ngayGioVe.Date < ngayGioDi.Date)
                    {
                        hd = null;
                        return hd;
                    }
                    ID_chuyenBayVe = TimChuyenBay(ngayGioVe, diemDen, diemDi, maLoaiVe, khachHang.Count);
                    if (ID_chuyenBayDi == null || ID_chuyenBayVe == null)
                        return hd;
                }
                if (ngayGioDi.Date < DateTime.Now.Date)
                {
                    hd = null;
                    return hd;
                }
                if (khachHang.Count == 0 || maLoaiVe == null || ngayGioDi == null || loaiChuyenBay == null || diemDi == null || diemDen == null)
                    return hd;
                else
                {
                    SqlTransaction trans;
                    provider.connection().Open(); // mo ket noi
                    trans = provider.connection().BeginTransaction(); // bat dau giao tac
                    SqlCommand command = new SqlCommand();
                    command.CommandType = CommandType.Text;
                    command.Connection = provider.connection();
                    command.Transaction = trans;
                    if (ID_chuyenBayDi != null) // neu la chuyen bay di
                    {
                        string strsql = "";
                        DataTable dt = new DataTable();
                        int Count = 0; // den xem co bao nhieu dong thoa man voi ID_chuyenBayDi
                        string strcount = "select count(*) from HoaDon where ID_chuyenBayDi = '" + ID_chuyenBayDi + "'";
                        command.CommandText = strcount;
                        // dt = provider.ExecuteQuery(strcount);
                        dt = provider.ExecuteQuery(command); // thuc thi cau lenh
                        DataRow dr = dt.Rows[0];
                        if (int.Parse(dr[0].ToString()) > 0)
                        {
                            Count = int.Parse(dr[0].ToString()); // ket qua
                        }

                        // xem gia ve
                        strsql = "select bgv.giaVe from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = " + maLoaiVe + " ";
                        dt = null;
                        double giave = 0.0;
                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (int.Parse(dt.Rows.Count.ToString()) > 0)
                            giave = double.Parse(dt.Rows[0]["giaVe"].ToString());
                        // tim phan tram gia ve tuy theo doi tuong
                        strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 1";
                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        double phanTramGiaVeNguoiLon = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());

                        strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 2";

                        //  dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        double phanTramGiaVeTremLonHon2NhoHon12 = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());

                        strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 3";

                        // dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        double phanTramGiaVeTrEmNhoHon2 = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());
                        int soVeNguoiLon = 0;  // tim so ve nguoi lon
                        int soVeNguoiTreEmLonHon2NhoHon12 = 0; // tim so ve tre em lon hon 2 tuoi nhung nho hon 12 tuoi
                        int soVeNguoiTreEmNhoHon2 = 0;    // tim so ve tre em nho hon 2 tuoi
                        for (int i = 0; i < khachHang.Count(); i++)
                            if (khachHang[i].LoaiDoiTuong == 1)
                            {
                                soVeNguoiLon++;
                            }
                            else
                                if (khachHang[i].LoaiDoiTuong == 2)
                                    soVeNguoiTreEmLonHon2NhoHon12++;
                                else
                                    soVeNguoiTreEmNhoHon2++;
                        // tinh tien ma cong ty dat cho phai tra
                        double tongTien = (soVeNguoiLon * phanTramGiaVeNguoiLon * giave + soVeNguoiTreEmLonHon2NhoHon12 * phanTramGiaVeTremLonHon2NhoHon12 * giave + soVeNguoiTreEmNhoHon2 * phanTramGiaVeTrEmNhoHon2 * giave) * 1.1;

                        string ngayDi1 = String.Format("{0:dd-MM-yyyy}", DateTime.Now);

                        if (loaiChuyenBay.Equals("Khu Hoi") && ID_chuyenBayVe != null) // neu that su ve nay la ve khu hoi va co chuyen bay ve
                        {

                            strsql = "select bgv.giaVe from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = '" + maLoaiVe + "' ";
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (int.Parse(dt.Rows.Count.ToString()) > 0)
                                giave = double.Parse(dt.Rows[0]["giaVe"].ToString());
                            tongTien += (soVeNguoiLon * phanTramGiaVeNguoiLon * giave + soVeNguoiTreEmLonHon2NhoHon12 * phanTramGiaVeTremLonHon2NhoHon12 * giave + soVeNguoiTreEmNhoHon2 * phanTramGiaVeTrEmNhoHon2 * giave) * 1.1;

                        }
                        string ID_hoaDon = "HD" + ID_chuyenBayDi + Count.ToString(); // tim ID hoa don
                        string ngayDi = String.Format("{0:yyyy-MM-dd}", DateTime.Now); // dinh dang chuoi ngay
                        strsql = "insert into HoaDon values('" + ID_hoaDon + "','" + ID_chuyenBayDi + "',null,'" + ngayDi + "'," + khachHang.Count + ",'" + HttpContext.Current.Session["Username"] + "'," + tongTien + ",0,0) ";
                        //  provider.excute(strsql);
                        command.CommandText = strsql;
                        provider.excute(command);// them hoa don vao he thong
                        // tao hoa don
                        hd.ID_hoaDon1 = ID_hoaDon;
                        hd.LoaiVe = maLoaiVe;
                        hd.LoaiChuyenBay = loaiChuyenBay;
                        hd.NgayDat = ngayDi1;
                        hd.TaiKhoan = HttpContext.Current.Session["Username"].ToString();
                        hd.TongTien = tongTien;

                        // Chuyen Bay Di
                        string soHieuMayBayDi = ""; // so hieu chuyen bay di
                        string soHieuMayBayVe = ""; // so hieu chuyen bay ve
                        strsql = "select mb.soHieuMayBay from ChuyenBay cb ,MayBay mb where cb.ID_chuyenBay = '" + ID_chuyenBayDi + "' and cb.ID_mayBay = mb.ID_mayBay";

                        //  dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (int.Parse(dt.Rows.Count.ToString()) > 0)
                        {
                            soHieuMayBayDi = dt.Rows[0]["soHieuMayBay"].ToString();

                        }
                        // Chuyen Bay Ve
                        if (loaiChuyenBay.Equals("Khu Hoi") && ID_chuyenBayVe != null)
                        {

                            strsql = "select mb.soHieuMayBay from ChuyenBay cb ,MayBay mb where cb.ID_chuyenBay = '" + ID_chuyenBayVe + "' and cb.ID_mayBay = mb.ID_mayBay";
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (int.Parse(dt.Rows.Count.ToString()) > 0)
                            {
                                soHieuMayBayVe = dt.Rows[0]["soHieuMayBay"].ToString();
                            }


                        }
                        // tinh so cho cho tung loai ve
                        int soGheLoaiVe1 = 0;
                        int soGheLoaiVe2 = 0;
                        int soGheLoaiVe3 = 0;
                        //   int soGheLoaiVe4 = 0;
                        //  int soGheLoaiVe5 = 0;
                        // int soGheLoaiVe6 = 0;
                        int soGheLoaiVe1_Ve = 0;
                        int soGheLoaiVe2_Ve = 0;
                        int soGheLoaiVe3_Ve = 0;
                        //  int soGheLoaiVe4_Ve = 0;
                        //  int soGheLoaiVe5_Ve = 0;
                        // int soGheLoaiVe6_Ve = 0;

                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = 1 ";

                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (dt.Rows.Count > 0)
                        {
                            soGheLoaiVe1 = int.Parse(dt.Rows[0]["soCho"].ToString());
                        }
                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = 2 ";

                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (dt.Rows.Count > 0)
                        {
                            soGheLoaiVe2 = int.Parse(dt.Rows[0]["soCho"].ToString());
                        }
                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = 3 ";

                        //dt = provider.ExecuteQuery(strsql);
                        command.CommandText = strsql;
                        dt = provider.ExecuteQuery(command);
                        if (dt.Rows.Count > 0)
                        {
                            soGheLoaiVe3 = int.Parse(dt.Rows[0]["soCho"].ToString());
                        }

                        //// tinh so ghe doi voi ve khu hoi

                        if (loaiChuyenBay.Equals("Khu Hoi") && ID_chuyenBayVe != null)
                        {
                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = 1 ";


                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (dt.Rows.Count > 0)
                            {
                                soGheLoaiVe1_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                            }
                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = 2 ";


                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (dt.Rows.Count > 0)
                            {
                                soGheLoaiVe2_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                            }
                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = 3 ";


                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (dt.Rows.Count > 0)
                            {
                                soGheLoaiVe3_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                            }

                        }
                        for (int i = 0; i < khachHang.Count; i++)
                        {


                            strsql = "";

                            string ID_KH = "";
                            strsql = "select ID_khach from Khach where soThe = '" + khachHang[i].SoThe + "'";
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            if (dt.Rows.Count > 0)
                            {
                                if (dt.Rows[0]["ID_khach"].ToString().Length > 0)
                                    ID_KH = dt.Rows[0]["ID_khach"].ToString();

                            }
                            else
                                if (ID_KH.Length < 1)
                                {
                                    ID_KH = "KH" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                    strsql = "insert into Khach values('" + ID_KH + "',null,'" + khachHang[i].HoTenKhachHang + "','" + khachHang[i].GioiTinh + "','" + khachHang[i].NgaySinh + "','" + khachHang[i].Email + "','" + khachHang[i].SoDienThoaiDiDong + "','" + khachHang[i].SoDienThoaiBan + "' ";
                                    strsql += "  ,'" + khachHang[i].LoaiGiayTo + "','" + khachHang[i].SoThe + "','" + khachHang[i].NuocPhatHanh + "','" + khachHang[i].QuocTich + "','" + khachHang[i].NgayHetHan + "'," + khachHang[i].LoaiDoiTuong + ",0)";

                                    command.CommandText = strsql;
                                    provider.excute(command);
                                }
                            // Chuyen Bay Di
                            strsql = "";
                            strsql = "select bgv.soChoHienTai from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayDi + "' and bgv.ID_loaiVe = '" + maLoaiVe + "'";
                            dt = null;
                            // dt = provider.ExecuteQuery(strsql);
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            int viTri = int.Parse(dt.Rows[0]["SoChoHienTai"].ToString());
                            if (int.Parse(maLoaiVe) == 1)
                            {
                                viTri = soGheLoaiVe1 - viTri + 1;
                            }
                            else
                                if (int.Parse(maLoaiVe) == 2)
                                {
                                    viTri = soGheLoaiVe1 + soGheLoaiVe2 - viTri + 1;
                                }
                                else
                                    if (int.Parse(maLoaiVe) == 3)
                                    {
                                        viTri = soGheLoaiVe1 + soGheLoaiVe2 + soGheLoaiVe3 - viTri + 1;
                                    }

                            string ID_ve = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                            strsql = "insert into VeMayBay values('" + ID_ve + "','" + ID_KH + "','" + maLoaiVe + "','" + ID_chuyenBayDi + "','" + viTri.ToString() + "','" + ngayDi + "',0,0)";
                            // provider.excute(strsql);
                            command.CommandText = strsql;
                            provider.excute(command);
                            strsql = "";
                            strsql = "insert into ChiTietHoaDon values('" + ID_hoaDon + "','" + ID_ve + "')"; // Chi Tiet Hoa Don
                            //   provider.excute(strsql);
                            command.CommandText = strsql;
                            provider.excute(command);
                            strsql = "update BangGiaVe set soChoHienTai = soChoHienTai - 1 where ID_chuyenBay = '" + ID_chuyenBayDi + "' and ID_loaiVe = " + maLoaiVe + "";
                            // provider.excute(strsql);
                            command.CommandText = strsql;
                            provider.excute(command);
                            // add vào Vé
                            VeMayBay vmb = new VeMayBay();
                            vmb.SoHieuMayBay = soHieuMayBayDi;
                            vmb.ID_ve1 = ID_ve;
                            vmb.TenKhachHang = khachHang[i].HoTenKhachHang;

                            if (khachHang[i].LoaiDoiTuong == 1)
                                vmb.GiaVe = giave * phanTramGiaVeNguoiLon;
                            else
                                if (khachHang[i].LoaiDoiTuong == 2)
                                    vmb.GiaVe = giave * phanTramGiaVeTremLonHon2NhoHon12;
                                else
                                    vmb.GiaVe = vmb.GiaVe = giave * phanTramGiaVeTrEmNhoHon2;
                            vmb.Thue = vmb.GiaVe * 0.1;
                            vmb.TongTien = vmb.GiaVe + vmb.Thue;
                            strsql = " select sb.thanhPho as thanhPhoDi , (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as ngayKhoiHanh ,";
                            strsql += " UPPER(SUBSTRING(DATENAME(DW,cbcb.ngayDi),0,3)) as ngayTrongTuanKhoiHanh ,(UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDi,108),0,3)) + UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDi,108),4,2))) as gioKhoiHanh ,";
                            strsql += " (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))+ '/' + UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as thoiHanVe ,";
                            strsql += " sb1.thanhPho as thanhPhoDen, (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as ngayDen, ";
                            strsql += " UPPER(SUBSTRING(DATENAME(DW,cbcb.ngayDen),0,3)) as ngayTrongTuanDen,(UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDen,108),0,3)) + UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDen,108),4,2))) as gioDen ,";
                            strsql += "cbcb.tongGioBay from SanBay sb,SanBay sb1,ChangBay_ChuyenBay cbcb,ChangBay cb where cbcb.ID_chuyenBay = '" + ID_chuyenBayDi + "'  and cbcb.ID_changBay = cb.ID_changBay and cb.ID_sanBayDi = sb.ID_sanBay and cb.ID_sanBayDen = sb1.ID_sanBay";
                            dt = null;
                            // dt = provider.ExecuteQuery(strsql);
                            command.CommandText = strsql;
                            dt = provider.ExecuteQuery(command);
                            List<ChiTietChangBayChuyenBay> listChiTietChuyenBay = new List<ChiTietChangBayChuyenBay>();
                            foreach (DataRow dr1 in dt.Rows)
                            {
                                ChiTietChangBayChuyenBay chitietCB = new ChiTietChangBayChuyenBay();
                                chitietCB.TenThanhPhoDi = dr1["thanhPhoDi"].ToString();
                                chitietCB.NgayKhoiHanh = dr1["ngayKhoiHanh"].ToString();
                                chitietCB.NgayTrongTuanKhoiHanh = dr1["gioKhoiHanh"].ToString();
                                chitietCB.GioKhoiHanh = dr1["ngayTrongTuanKhoiHanh"].ToString();
                                chitietCB.ThoiHanVe = dr1["thoiHanVe"].ToString();
                                chitietCB.ViTri = viTri.ToString();
                                chitietCB.TenThanhPhoDen = dr1["thanhPhoDen"].ToString();
                                chitietCB.NgayDen = dr1["ngayDen"].ToString();
                                chitietCB.NgayTrongTuanDen = dr1["ngayTrongTuanDen"].ToString();
                                chitietCB.GioKhoiHanh = dr1["gioDen"].ToString();
                                chitietCB.TongThoiGianBay = dr1["tongGioBay"].ToString();
                                listChiTietChuyenBay.Add(chitietCB); // them chi tiet cac chang bay vao danh sach chi tiet chuyen bay

                            }

                            vmb.ListChiTiet = listChiTietChuyenBay; // gan danh sach chi tiet bay vào ve may bay

                            listVeMayBay.Add(vmb);// them ve may bay vao danh sach ve

                            // Chuyen Bay Ve
                            if (loaiChuyenBay.Equals("Khu Hoi") && ID_chuyenBayVe != null)
                            {
                                strsql = "select bgv.soChoHienTai from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ID_chuyenBayVe + "' and bgv.ID_loaiVe = '" + maLoaiVe + "'";
                                dt = null;
                                // dt = provider.ExecuteQuery(strsql);
                                command.CommandText = strsql;
                                dt = provider.ExecuteQuery(command);
                                int viTri_Ve = int.Parse(dt.Rows[0]["SoChoHienTai"].ToString());
                                if (int.Parse(maLoaiVe) == 1)
                                {
                                    viTri_Ve = soGheLoaiVe1_Ve - viTri_Ve + 1;
                                }
                                else
                                    if (int.Parse(maLoaiVe) == 2)
                                    {
                                        viTri_Ve = soGheLoaiVe1_Ve + soGheLoaiVe2_Ve - viTri_Ve + 1;
                                    }
                                    else
                                        if (int.Parse(maLoaiVe) == 3)
                                        {
                                            viTri_Ve = soGheLoaiVe1_Ve + soGheLoaiVe2_Ve + soGheLoaiVe3_Ve - viTri_Ve + 1;
                                        }

                                string ID_ve_ChuyenVe = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                strsql = "insert into VeMayBay values('" + ID_ve_ChuyenVe + "','" + ID_KH + "','" + maLoaiVe + "','" + ID_chuyenBayVe + "','" + viTri.ToString() + "','" + ngayDi + "',0,0)";
                                // provider.excute(strsql);
                                command.CommandText = strsql;
                                provider.excute(command);
                                //update HoaDon
                                strsql = "update HoaDon set ID_chuyenBayVe = '" + ID_chuyenBayVe + "' where ID_hoaDon = '" + ID_hoaDon + "'";
                                command.CommandText = strsql;
                                provider.excute(command);
                                // insert into ChiTietHoaDon
                                strsql = "";
                                strsql = "insert into ChiTietHoaDon values('" + ID_hoaDon + "','" + ID_ve_ChuyenVe + "')";
                                command.CommandText = strsql;
                                provider.excute(command);
                                // update BangGiaVe
                                strsql = "update BangGiaVe set soChoHienTai = soChoHienTai - 1 where ID_chuyenBay = '" + ID_chuyenBayVe + "' and ID_loaiVe = " + maLoaiVe + "";
                                command.CommandText = strsql;
                                provider.excute(command);
                                // add vào Vé
                                vmb = new VeMayBay();
                                vmb.SoHieuMayBay = soHieuMayBayVe;
                                vmb.ID_ve1 = ID_ve_ChuyenVe;
                                vmb.TenKhachHang = khachHang[i].HoTenKhachHang;

                                if (khachHang[i].LoaiDoiTuong == 1)
                                    vmb.GiaVe = giave * phanTramGiaVeNguoiLon;
                                else
                                    if (khachHang[i].LoaiDoiTuong == 2)
                                        vmb.GiaVe = giave * phanTramGiaVeTremLonHon2NhoHon12;
                                    else
                                        vmb.GiaVe = vmb.GiaVe = giave * phanTramGiaVeTrEmNhoHon2;
                                vmb.Thue = vmb.GiaVe * 0.1;// thue 10%
                                vmb.TongTien = vmb.GiaVe + vmb.Thue; // tong tien
                                strsql = " select sb.thanhPho as thanhPhoDi , (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as ngayKhoiHanh ,";
                                strsql += " UPPER(SUBSTRING(DATENAME(DW,cbcb.ngayDi),0,3)) as ngayTrongTuanKhoiHanh ,(UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDi,108),0,3)) + UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDi,108),4,2))) as gioKhoiHanh ,";
                                strsql += " (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))+ '/' + UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as thoiHanVe ,";
                                strsql += " sb1.thanhPho as thanhPhoDen, (UPPER(SUBSTRING(DATENAME(D,cbcb.ngayDi),0,3)) + UPPER(SUBSTRING(DATENAME(M,cbcb.ngayDi),0,4)) +  UPPER(SUBSTRING(DATENAME(YY,cbcb.ngayDi),3,2))) as ngayDen, ";
                                strsql += " UPPER(SUBSTRING(DATENAME(DW,cbcb.ngayDen),0,3)) as ngayTrongTuanDen,(UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDen,108),0,3)) + UPPER(SUBSTRING(CONVERT(varchar,cbcb.gioDen,108),4,2))) as gioDen ,";
                                strsql += "cbcb.tongGioBay from SanBay sb,SanBay sb1,ChangBay_ChuyenBay cbcb,ChangBay cb where cbcb.ID_chuyenBay = '" + ID_chuyenBayVe + "'  and cbcb.ID_changBay = cb.ID_changBay and cb.ID_sanBayDi = sb.ID_sanBay and cb.ID_sanBayDen = sb1.ID_sanBay";
                                dt = null;
                                // dt = provider.ExecuteQuery(strsql);
                                command.CommandText = strsql;
                                dt = provider.ExecuteQuery(command);

                                listChiTietChuyenBay = new List<ChiTietChangBayChuyenBay>();// khai bao danh sach chi tiet chuyen bay
                                foreach (DataRow dr1 in dt.Rows)
                                {
                                    ChiTietChangBayChuyenBay chitietCB = new ChiTietChangBayChuyenBay();// khai bao chi tiet chang bay
                                    chitietCB.TenThanhPhoDi = dr1["thanhPhoDi"].ToString();
                                    chitietCB.NgayKhoiHanh = dr1["ngayKhoiHanh"].ToString();
                                    chitietCB.NgayTrongTuanKhoiHanh = dr1["gioKhoiHanh"].ToString();
                                    chitietCB.GioKhoiHanh = dr1["ngayTrongTuanKhoiHanh"].ToString();
                                    chitietCB.ThoiHanVe = dr1["thoiHanVe"].ToString();
                                    chitietCB.ViTri = viTri.ToString();
                                    chitietCB.TenThanhPhoDen = dr1["thanhPhoDen"].ToString();
                                    chitietCB.NgayDen = dr1["ngayDen"].ToString();
                                    chitietCB.NgayTrongTuanDen = dr1["ngayTrongTuanDen"].ToString();
                                    chitietCB.GioKhoiHanh = dr1["gioDen"].ToString();
                                    chitietCB.TongThoiGianBay = dr1["tongGioBay"].ToString();
                                    listChiTietChuyenBay.Add(chitietCB);// them chang bay vao danh sach chi tiet chuyen bay

                                }

                                vmb.ListChiTiet = listChiTietChuyenBay;  // gan danh sach chi tiet

                                listVeMayBay.Add(vmb); // them ve may bay vao danh sach ve may bay

                            }

                        }

                        hd.ListVe = listVeMayBay; // gan danh sach ve vao hoa don
                    }
                    trans.Commit();// cam ket giao tac thanh cong
                    provider.closeConnection();// dong ket noi
                }

            }
            catch
            {
                provider.closeConnection();// dong ket noi khi gap loi
                hd = null;

            }
        }
        else
            hd = null;
        return hd;

    }


    /*
     * @phuong thuc timKiemChuyenBay : tim kiem chuyen bay
     * @tham so
     *   1.soLuong int : so luong khach ngoi
     *   2. maLoaiVe string : ma loai ve co dinh dang nam trong (1,2,3,4,5,6)
     *   3. ngayDi DateTime : ngay  di
     *   4. ngayVe DateTime : ngay  Ve
     *   5. loaiChuyenBay String : la ve thuoc khu hoi hay mot chieu co dang (Mot Chieu , Khu Hoi)
     *   6. diemDi string : ma san bay di
     *   7. diemDen : string : ma san bay den
     * @ ket qua tra ve List<ChuyenBay>
     *    danh sach cac chuyen bay thoa man
     * 
     * */
    public List<ChuyenBay1> TimKiemChuyenBay1(int soLuong, string maLoaiVe, DateTime ngayDi, DateTime ngayVe, string loaiChuyenBay, string diemDi, string diemDen)
    {
        List<ChuyenBay1> listChuyenBay = new List<ChuyenBay1>();
        if (soLuong == 0 || maLoaiVe == null || ngayDi == null || ngayVe == null || loaiChuyenBay == null || diemDi == null || diemDen == null)
        {
            listChuyenBay = null;
            return listChuyenBay;
        }
        if (ngayDi.Date < DateTime.Now.Date)
        {
            listChuyenBay = null;
            return listChuyenBay;
        }
        try
        {

            string strsql = "select cbcb.ngayDi,cbcb.gioDi,(select cbcb2.ngayDen from ChangBay_ChuyenBay cbcb2,ChuyenBay cb2  where cbcb2.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb2.ID_chuyenBay = cb2.ID_chuyenBay and cbcb2.changThu = cb2.soChang  ) as ngayDen, ";
            strsql += " (select cbcb2.gioDen from ChangBay_ChuyenBay cbcb2,ChuyenBay cb2  where cbcb2.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb2.ID_chuyenBay = cb2.ID_chuyenBay and cbcb2.changThu = cb2.soChang  ) as gioDen , bgv.giaVe,bgv.donViTien";
            strsql += " from ChuyenBay cb ,ChangBay_ChuyenBay cbcb ,BangGiaVe bgv,ChangBay c";
            strsql += " where bgv.ID_loaiVe = " + maLoaiVe + " and bgv.ID_chuyenBay = cb.ID_chuyenBay and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay and cb.tinhTrang = 0 ";
            strsql += " and (c.ID_sanBayDi = '" + diemDi + "' and cbcb.changThu = 1) and  cbcb.ngayDi = '" + ngayDi + "' and bgv.soChoHienTai >= " + soLuong + " ";
            strsql += " and '" + diemDen + "' in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang )  ";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(strsql);
            foreach (DataRow dr in dt.Rows)
            {
                ChuyenBay1 cb = new ChuyenBay1();
                cb.MaSanBayDi = diemDi;
                cb.MaSanBayDen = diemDen;
                cb.LoaiChuyenBay = "Chuyen Di";
                cb.NgayDi = dr["ngayDi"].ToString();
                cb.GioDi = dr["gioDi"].ToString();
                cb.NgayDen = dr["ngayDen"].ToString();
                cb.GioDen = dr["gioDen"].ToString();
                cb.GiaVe = Double.Parse(dr["giaVe"].ToString());
                cb.DonViTien = dr["donViTien"].ToString();
                listChuyenBay.Add(cb);
            }
            if (loaiChuyenBay.Equals("Khu Hoi"))
            {
                if (ngayVe.Date < DateTime.Now.Date || ngayVe.Date < ngayDi.Date)
                {
                    listChuyenBay = null;
                    return listChuyenBay;
                }
                string strsql1 = "select cbcb.ngayDi,cbcb.gioDi,(select cbcb2.ngayDen from ChangBay_ChuyenBay cbcb2,ChuyenBay cb2  where cbcb2.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb2.ID_chuyenBay = cb2.ID_chuyenBay and cbcb2.changThu = cb2.soChang  ) as ngayDen , ";
                strsql1 += " (select cbcb2.gioDen from ChangBay_ChuyenBay cbcb2,ChuyenBay cb2  where cbcb2.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb2.ID_chuyenBay = cb2.ID_chuyenBay and cbcb2.changThu = cb2.soChang  ) as gioDen , bgv.giaVe,bgv.donViTien";
                strsql1 += " from ChuyenBay cb ,ChangBay_ChuyenBay cbcb ,BangGiaVe bgv,ChangBay c ";
                strsql1 += " where bgv.ID_loaiVe = " + maLoaiVe + " and bgv.ID_chuyenBay = cb.ID_chuyenBay and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay and cb.tinhTrang = 0";
                strsql1 += " and (c.ID_sanBayDi = '" + diemDen + "' and cbcb.changThu = 1) and  cbcb.ngayDi = '" + ngayVe + "' and bgv.soChoHienTai >= " + soLuong + " ";
                strsql1 += " and '" + diemDi + "' in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang )  ";
                DataTable dt1 = new DataTable();
                dt1 = provider.ExecuteQuery(strsql1);
                if (dt1.Rows.Count == 0)
                {
                    listChuyenBay = null;
                    return listChuyenBay;
                }
                else
                {
                    foreach (DataRow dr1 in dt1.Rows)
                    {
                        ChuyenBay1 cb = new ChuyenBay1();
                        cb.MaSanBayDi = diemDen;
                        cb.MaSanBayDen = diemDi;
                        cb.LoaiChuyenBay = "Chuyen Ve";
                        cb.NgayDi = dr1["ngayDi"].ToString();
                        cb.GioDi = dr1["gioDi"].ToString();
                        cb.NgayDen = dr1["ngayDen"].ToString();
                        cb.GioDen = dr1["gioDen"].ToString();
                        cb.GiaVe = Double.Parse(dr1["giaVe"].ToString());
                        cb.DonViTien = dr1["donViTien"].ToString();
                        listChuyenBay.Add(cb);
                    }

                }
            }

        }
        catch
        {
            listChuyenBay = null;
        }
        return listChuyenBay;
    }
    /*
     * @phuong thuc TimKiemChuyenBay : tim kiem chuyen bay
     * @tham so
     *   1. sanBayDi string : mã sân bay đi
     *   2. sanBayDen string : mã sân bay đến
     *   3. thoiGianDi DateTime : thời gian đi
     *   4. thoiGianVe DateTime : thời gian về
     *   5. loaiChuyenBay string : ma loai ve co dinh dang nam trong (Khu Hoi, Mot Chieu)
     *   
     * @ ket qua tra ve List<ChuyenBay>
     *    danh sach cac chuyen bay thoa man
     * 
     * */
    public List<ChuyenBay> TimKiemChuyenBay(string sanBayDi, string sanBayDen, DateTime thoiGianDi, DateTime thoiGianVe, string loaiChuyenBay)
    {

        List<ChuyenBay> listChuyenBay = new List<ChuyenBay>();
        if (loaiChuyenBay == null || thoiGianDi == null || sanBayDi == null || sanBayDen == null)
        {
            listChuyenBay = null;
            return listChuyenBay;
        }
        if (thoiGianDi.Date < DateTime.Now.Date)
        {
            listChuyenBay = null;
            return listChuyenBay;
        }

        try
        {

            string strsql = "select distinct cb.ID_chuyenBay,cbcb.ngayDi,(select cbcb2.ngayDen from ChangBay_ChuyenBay cbcb2,ChuyenBay cb2  where cbcb2.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb2.ID_chuyenBay = cb2.ID_chuyenBay and cbcb2.changThu = cb2.soChang  ) as ngayDen, ";
            strsql += " bgv.donViTien";
            strsql += " from ChuyenBay cb ,ChangBay_ChuyenBay cbcb ,BangGiaVe bgv,ChangBay c";
            strsql += " where  bgv.ID_chuyenBay = cb.ID_chuyenBay and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay and cb.tinhTrang = 0";
            strsql += " and (c.ID_sanBayDi = '" + sanBayDi + "' and cbcb.changThu = 1) and  cbcb.ngayDi = '" + thoiGianDi + "' ";
            strsql += " and '" + sanBayDen + "' in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang )  ";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(strsql);
            foreach (DataRow dr in dt.Rows)
            {
                ChuyenBay cb = new ChuyenBay();
                List<string> CacHangGhe = new List<string>();
                List<double> gia = new List<double>();
                cb.MaChuyenBay = dr["ID_chuyenBay"].ToString();
                cb.MaSanBayDi = sanBayDi;
                cb.MaSanBayDen = sanBayDen;
                cb.Loai = "Mot Chieu";
                cb.ThoiGianDi = DateTime.Parse(dr["ngayDi"].ToString());
                cb.ThoiGianDen = DateTime.Parse(dr["ngayDen"].ToString());

                cb.DonViTien = dr["donViTien"].ToString();
                DataTable dt1 = new DataTable();
                dt = provider.ExecuteQuery(strsql);
                strsql = "select   ID_loaiVe,giaVe";
                strsql += " from BangGiaVe bgv where ID_chuyenBay = '" + cb.MaChuyenBay + "'";
                dt1 = provider.ExecuteQuery(strsql);
                foreach (DataRow dr1 in dt1.Rows)
                {
                    if (int.Parse(dr1["ID_loaiVe"].ToString()) == 1)
                        CacHangGhe.Add("Thuong Nhan");
                    else
                        if (int.Parse(dr1["ID_loaiVe"].ToString()) == 2)
                            CacHangGhe.Add("Pho Thong");
                        else
                            CacHangGhe.Add("Tiet Kiem");
                    gia.Add(double.Parse(dr1["giaVe"].ToString()));
                }
                cb.Gia = gia;
                cb.CacHangGhe = CacHangGhe;
                listChuyenBay.Add(cb);
            }
            if (loaiChuyenBay.Equals("Khu Hoi"))
            {
                if (thoiGianVe.Date == null || thoiGianVe.Date < DateTime.Now.Date || thoiGianVe.Date < thoiGianDi.Date)
                {
                    listChuyenBay = null;
                    return listChuyenBay;
                }
                strsql = "select distinct cb.ID_chuyenBay,cbcb.ngayDi,(select cbcb2.ngayDen from ChangBay_ChuyenBay cbcb2,ChuyenBay cb2  where cbcb2.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb2.ID_chuyenBay = cb2.ID_chuyenBay and cbcb2.changThu = cb2.soChang  ) as ngayDen, ";
                strsql += " bgv.donViTien";
                strsql += " from ChuyenBay cb ,ChangBay_ChuyenBay cbcb ,BangGiaVe bgv,ChangBay c";
                strsql += " where  bgv.ID_chuyenBay = cb.ID_chuyenBay and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay and cb.tinhTrang = 0 ";
                strsql += " and (c.ID_sanBayDi = '" + sanBayDen + "' and cbcb.changThu = 1) and  cbcb.ngayDi = '" + thoiGianVe + "'";
                strsql += " and '" + sanBayDi + "' in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang )  ";

                dt = provider.ExecuteQuery(strsql);


                foreach (DataRow dr in dt.Rows)
                {
                    ChuyenBay cb = new ChuyenBay();
                    List<string> CacHangGhe = new List<string>();
                    List<double> gia = new List<double>();
                    cb.MaChuyenBay = dr["ID_chuyenBay"].ToString();
                    cb.MaSanBayDi = sanBayDen;
                    cb.MaSanBayDen = sanBayDi;
                    cb.Loai = "Khu Hoi";
                    cb.ThoiGianDi = DateTime.Parse(dr["ngayDi"].ToString());
                    cb.ThoiGianDen = DateTime.Parse(dr["ngayDen"].ToString());

                    cb.DonViTien = dr["donViTien"].ToString();
                    DataTable dt1 = new DataTable();
                    dt = provider.ExecuteQuery(strsql);
                    strsql = "select   ID_loaiVe,giaVe";
                    strsql += " from BangGiaVe bgv where ID_chuyenBay = '" + cb.MaChuyenBay + "'";
                    dt1 = provider.ExecuteQuery(strsql);
                    if (dt1.Rows.Count == 0)
                    {
                        listChuyenBay = null;
                        return listChuyenBay;
                    }
                    foreach (DataRow dr1 in dt1.Rows)
                    {
                        if (int.Parse(dr1["ID_loaiVe"].ToString()) == 1)
                            CacHangGhe.Add("Thuong Nhan");
                        else
                            if (int.Parse(dr1["ID_loaiVe"].ToString()) == 2)
                                CacHangGhe.Add("Pho Thong");
                            else
                                CacHangGhe.Add("Tiet Kiem");
                        gia.Add(double.Parse(dr1["giaVe"].ToString()));
                    }
                    cb.Gia = gia;
                    cb.CacHangGhe = CacHangGhe;
                    listChuyenBay.Add(cb);

                }
                for (int j = 0; j < listChuyenBay.Count; j++)
                {
                    listChuyenBay[j].Loai = "Khu Hoi";
                }



            }
        }
        catch
        {
            listChuyenBay = null;
        }
        return listChuyenBay;
    }
    /*
     *@Phuong thuc TimKiemChuyenBay2
     *@tham so:
     *    1. sanBayDi : ma san bay di
     *    2. sanBayDen : ma san bay den
     * ket qua tra ve
     *    1. danh sach chuyen bay neu thanh cong
     *    2. null neu khong timf thay.
     * */
    public List<ChuyenBay> TimKiemChuyenBay2(string sanBayDi, string sanBayDen)
    {
        List<ChuyenBay> listChuyenBay = new List<ChuyenBay>();
        if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
        {
            if (sanBayDi == null || sanBayDen == null)
            {
                listChuyenBay = null;
                return listChuyenBay;
            }
            else
            {
                try
                {
                    string thoiGianDi = String.Format("{0:yyyy-MM-dd}", DateTime.Now);
                    string strsql = "select distinct cb.ID_chuyenBay,cbcb.ngayDi,(select cbcb2.ngayDen from ChangBay_ChuyenBay cbcb2,ChuyenBay cb2  where cbcb2.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb2.ID_chuyenBay = cb2.ID_chuyenBay and cbcb2.changThu = cb2.soChang  ) as ngayDen, ";
                    strsql += " bgv.donViTien";
                    strsql += " from ChuyenBay cb ,ChangBay_ChuyenBay cbcb ,BangGiaVe bgv,ChangBay c";
                    strsql += " where  bgv.ID_chuyenBay = cb.ID_chuyenBay and cb.ID_chuyenBay = cbcb.ID_chuyenBay and cbcb.ID_changBay = c.ID_changBay and cb.tinhTrang = 0";
                    strsql += " and (c.ID_sanBayDi = '" + sanBayDi + "' and cbcb.changThu = 1) and  cbcb.ngayDi >= '" + thoiGianDi + "' ";
                    strsql += " and '" + sanBayDen + "' in (select c1.ID_sanBayDen  from ChangBay_ChuyenBay cbcb1,ChangBay c1 where cbcb1.ID_chuyenBay = cb.ID_chuyenBay and cbcb1.ID_changBay = c1.ID_changBay and cbcb1.changThu = cb.soChang )  ";
                    DataTable dt = new DataTable();
                    dt = provider.ExecuteQuery(strsql);
                    foreach (DataRow dr in dt.Rows)
                    {
                        ChuyenBay cb = new ChuyenBay();
                        List<string> CacHangGhe = new List<string>();
                        List<double> gia = new List<double>();
                        cb.MaChuyenBay = dr["ID_chuyenBay"].ToString();
                        cb.MaSanBayDi = sanBayDi;
                        cb.MaSanBayDen = sanBayDen;
                        cb.Loai = "Mot Chieu";
                        cb.ThoiGianDi = DateTime.Parse(dr["ngayDi"].ToString());
                        cb.ThoiGianDen = DateTime.Parse(dr["ngayDen"].ToString());

                        cb.DonViTien = dr["donViTien"].ToString();
                        DataTable dt1 = new DataTable();
                        dt = provider.ExecuteQuery(strsql);
                        strsql = "select   ID_loaiVe,giaVe";
                        strsql += " from BangGiaVe bgv where ID_chuyenBay = '" + cb.MaChuyenBay + "'";
                        dt1 = provider.ExecuteQuery(strsql);
                        foreach (DataRow dr1 in dt1.Rows)
                        {
                            if (int.Parse(dr1["ID_loaiVe"].ToString()) == 1)
                                CacHangGhe.Add("Thuong Nhan");
                            else
                                if (int.Parse(dr1["ID_loaiVe"].ToString()) == 2)
                                    CacHangGhe.Add("Pho Thong");
                                else
                                    CacHangGhe.Add("Tiet Kiem");
                            gia.Add(double.Parse(dr1["giaVe"].ToString()));
                        }
                        cb.Gia = gia;
                        cb.CacHangGhe = CacHangGhe;
                        listChuyenBay.Add(cb);
                    }
                }

                catch
                {
                    listChuyenBay = null;
                    return listChuyenBay;
                }

            }
        }
        else
            listChuyenBay = null;
        return listChuyenBay;
    }

    /*
     * @phuong thuc KiemTraChuyenBay : kiem tra chuyen bay do ton tai trong he thong hay khong
     * @tham so 
     *    maChuyenBay : string
     * @tra ve: true neu ton tai
     *          false neu khong ton tai
     * */
    public bool KiemTraChuyenBay(string maChuyebay)
    {
        bool kq = false;
        string strsql = "select * from ChuyenBay where  ID_chuyenBay = '" + maChuyebay + "' and tinhTrang = 0 ";
        DataTable dt = new DataTable();
        dt = provider.ExecuteQuery(strsql);
        if (dt.Rows.Count > 0)
            kq = true;
        return kq;
    }
    /*
     * @phuong thuc KiemTraChuyenBay : kiem tra chuyen bay do ton tai trong he thong hay khong
     * @tham so 
     *    maChuyenBay : string
     * @tra ve: true neu ton tai
     *          false neu khong ton tai
     * */
    public bool KiemTraChuyenBay(string maChuyebay, int soVe, int maLoaiVe)
    {
        bool kq = false;
        string strsql = "select * from BangGiaVe where  ID_chuyenBay = '" + maChuyebay + "' and ID_loaiVe = " + maLoaiVe + " and soChoHienTai >= " + soVe + " ";
        DataTable dt = new DataTable();
        dt = provider.ExecuteQuery(strsql);
        if (dt.Rows.Count > 0)
            kq = true;
        return kq;
    }
    /*
     * @Phuong Thuc DatVe : dung de dat ve
     * @Tham So : 
     *    1. ChuyenBayDi string Ma Chuyen Bay Di
     *    2. ChuyenBayVe string Ma Chuyen Bay Ve
     *    3. dsHanhKhach List<KhachHang> dskhachHang
     *    4. Hang List<string> hang
     * */
    //, 
    public string DatVe(string ChuyenBayDi, string ChuyenBayVe, List<KhachHang> dsHanhKhach, List<string> Hang, DateTime ngayDi, DateTime ngayVe)
    {
        string kq = "";
            try
            {
                List<VeMayBay> listVeMayBay = new List<VeMayBay>(); // danh sach ve
                int soVeHang1 = 0;
                int soVeHang2 = 0;
                int soVeHang3 = 0;

                List<int> maLoaiVe = new List<int>();// ma loai ve

                for (int i = 0; i < Hang.Count; i++)
                {
                    if (Hang[i].Equals("Thuong Nhan"))
                    {
                        soVeHang1++;
                        maLoaiVe.Add(1);
                    }
                    else
                        if (Hang[i].Equals("Pho Thong"))
                        {
                            soVeHang2++;
                            maLoaiVe.Add(2);
                        }
                        else
                            if (Hang[i].Equals("Tiet Kiem"))
                            {
                                soVeHang3++;
                                maLoaiVe.Add(3);
                            }
                            else
                                return kq = "3";
                }
                if (soVeHang1 > 0 && !KiemTraChuyenBay(ChuyenBayDi, soVeHang1, 1))
                {
                    return kq = "3";
                }
                else
                    if (soVeHang2 > 0 && !KiemTraChuyenBay(ChuyenBayDi, soVeHang2, 2))
                        return kq = "3";
                    else
                        if (soVeHang3 > 0 && !KiemTraChuyenBay(ChuyenBayDi, soVeHang3, 3))
                            return kq = "3";
                if (dsHanhKhach.Count == 0 || dsHanhKhach.Count != Hang.Count)
                    return kq = "2";
                else
                    if (Hang.Count == 0)
                        return kq = "3";
                    else
                        if (ChuyenBayDi == null || ChuyenBayDi.Length == 0)
                            return kq = "0";
                        else
                            if (KiemTraChuyenBay(ChuyenBayDi) == false)
                                return kq = "0";
                            else
                                if (ChuyenBayVe != "" && ChuyenBayVe.Length > 0 && KiemTraChuyenBay(ChuyenBayVe) == false)
                                    return kq = "1";
                                else
                                {
                                    SqlTransaction trans;
                                    provider.connection().Open(); // mo ket noi
                                    trans = provider.connection().BeginTransaction(); // bat dau giao tac
                                    SqlCommand command = new SqlCommand();
                                    command.CommandType = CommandType.Text;
                                    command.Connection = provider.connection();
                                    command.Transaction = trans;

                                    string strsql = "";

                                    DataTable dt = new DataTable();


                                    int Count = 0; // den xem co bao nhieu dong thoa man voi ID_chuyenBayDi
                                    string strcount = "select count(*) from HoaDon where ID_chuyenBayDi = '" + ChuyenBayDi + "'";
                                    command.CommandText = strcount;
                                    // dt = provider.ExecuteQuery(strcount);
                                    dt = provider.ExecuteQuery(command); // thuc thi cau lenh
                                    DataRow dr = dt.Rows[0];
                                    if (int.Parse(dr[0].ToString()) > 0)
                                    {
                                        Count = int.Parse(dr[0].ToString()); // ket qua
                                    }

                                    // xem gia ve
                                    strsql = "select bgv.giaVe from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' ";
                                    dt = null;
                                    double giaveLoai1 = 0.0;
                                    double giaveLoai2 = 0.0;
                                    double giaveLoai3 = 0.0;
                                    //dt = provider.ExecuteQuery(strsql);
                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    if (int.Parse(dt.Rows.Count.ToString()) > 0)
                                    {

                                        giaveLoai1 = double.Parse(dt.Rows[0]["giaVe"].ToString());
                                        giaveLoai2 = double.Parse(dt.Rows[1]["giaVe"].ToString());
                                        giaveLoai3 = double.Parse(dt.Rows[2]["giaVe"].ToString());
                                    }
                                    // tim phan tram gia ve tuy theo doi tuong
                                    strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 1";
                                    //dt = provider.ExecuteQuery(strsql);
                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    double phanTramGiaVeNguoiLon = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());

                                    strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 2";

                                    //  dt = provider.ExecuteQuery(strsql);
                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    double phanTramGiaVeTremLonHon2NhoHon12 = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());

                                    strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 3";

                                    // dt = provider.ExecuteQuery(strsql);
                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    double phanTramGiaVeTrEmNhoHon2 = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());
                                    int soVeNguoiLon_Veloai1 = 0;  // tim so ve nguoi lon ve loai 1
                                    int soVeNguoiLon_Veloai2 = 0;  // tim so ve nguoi lon ve laoi 2
                                    int soVeNguoiLon_Veloai3 = 0;  // tim so ve nguoi lon ve loai 3
                                    int soVeTreEmLonHon2NhoHon12_Veloai1 = 0; // tim so ve tre em lon hon 2 tuoi nhung nho hon 12 tuoi ve loai 1
                                    int soVeTreEmLonHon2NhoHon12_Veloai2 = 0; // tim so ve tre em lon hon 2 tuoi nhung nho hon 12 tuoi ve loai 2
                                    int soVeTreEmLonHon2NhoHon12_Veloai3 = 0; // tim so ve tre em lon hon 2 tuoi nhung nho hon 12 tuoi ve loai 3
                                    int soVeTreEmNhoHon2_Veloai1 = 0;    // tim so ve tre em nho hon 2 tuoi loai 1
                                    int soVeTreEmNhoHon2_Veloai2 = 0;    // tim so ve tre em nho hon 2 tuoi laoi 2
                                    int soVeTreEmNhoHon2_Veloai3 = 0;    // tim so ve tre em nho hon 2 tuoi loai 3
                                    for (int i = 0; i < dsHanhKhach.Count(); i++)
                                        if (dsHanhKhach[i].LoaiDoiTuong == 1 && maLoaiVe[i] == 1)
                                        {
                                            soVeNguoiLon_Veloai1++;
                                        }
                                        else
                                            if (dsHanhKhach[i].LoaiDoiTuong == 1 && maLoaiVe[i] == 2)
                                                soVeNguoiLon_Veloai2++;
                                            else
                                                if (dsHanhKhach[i].LoaiDoiTuong == 1 && maLoaiVe[i] == 3)
                                                    soVeNguoiLon_Veloai3++;
                                                else
                                                    if (dsHanhKhach[i].LoaiDoiTuong == 2 && maLoaiVe[i] == 1)
                                                        soVeTreEmLonHon2NhoHon12_Veloai1++;
                                                    else
                                                        if (dsHanhKhach[i].LoaiDoiTuong == 2 && maLoaiVe[i] == 2)
                                                            soVeTreEmLonHon2NhoHon12_Veloai2++;
                                                        else
                                                            if (dsHanhKhach[i].LoaiDoiTuong == 2 && maLoaiVe[i] == 3)
                                                                soVeTreEmLonHon2NhoHon12_Veloai3++;
                                                            else
                                                                if (dsHanhKhach[i].LoaiDoiTuong == 3 && maLoaiVe[i] == 1)
                                                                    soVeTreEmNhoHon2_Veloai1++;
                                                                else
                                                                    if (dsHanhKhach[i].LoaiDoiTuong == 3 && maLoaiVe[i] == 2)
                                                                        soVeTreEmNhoHon2_Veloai2++;
                                                                    else
                                                                        soVeTreEmNhoHon2_Veloai3++;
                                    // tinh tien ma cong ty dat cho phai tra
                                    double tongTien = ((soVeNguoiLon_Veloai1 * giaveLoai1 + soVeNguoiLon_Veloai2 * giaveLoai2 + soVeNguoiLon_Veloai3 * giaveLoai3) + (soVeTreEmLonHon2NhoHon12_Veloai1 * giaveLoai1 + soVeTreEmLonHon2NhoHon12_Veloai2 * giaveLoai2 + soVeTreEmLonHon2NhoHon12_Veloai3 * giaveLoai3) * phanTramGiaVeTremLonHon2NhoHon12 + (soVeTreEmNhoHon2_Veloai1 * giaveLoai1 + soVeTreEmNhoHon2_Veloai2 * giaveLoai2 + soVeTreEmNhoHon2_Veloai3 * giaveLoai3) * phanTramGiaVeTrEmNhoHon2) * 1.1;

                                    string ngayDi1 = String.Format("{0:dd-MM-yyyy}", ngayDi.Date);

                                    if (ChuyenBayVe != "") // neu that su ve nay la ve khu hoi va co chuyen bay ve
                                    {
                                        // xem gia ve
                                        strsql = "select bgv.giaVe from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' ";
                                        dt = null;
                                        giaveLoai1 = 0.0;
                                        giaveLoai2 = 0.0;
                                        giaveLoai3 = 0.0;
                                        //dt = provider.ExecuteQuery(strsql);
                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        if (int.Parse(dt.Rows.Count.ToString()) > 0)
                                        {

                                            giaveLoai1 = double.Parse(dt.Rows[0]["giaVe"].ToString());
                                            giaveLoai2 = double.Parse(dt.Rows[1]["giaVe"].ToString());
                                            giaveLoai3 = double.Parse(dt.Rows[2]["giaVe"].ToString());
                                        }
                                        tongTien += ((soVeNguoiLon_Veloai1 * giaveLoai1 + soVeNguoiLon_Veloai2 * giaveLoai2 + soVeNguoiLon_Veloai3 * giaveLoai3) + (soVeTreEmLonHon2NhoHon12_Veloai1 * giaveLoai1 + soVeTreEmLonHon2NhoHon12_Veloai2 * giaveLoai2 + soVeTreEmLonHon2NhoHon12_Veloai3 * giaveLoai3) * phanTramGiaVeTremLonHon2NhoHon12 + (soVeTreEmNhoHon2_Veloai1 * giaveLoai1 + soVeTreEmNhoHon2_Veloai2 * giaveLoai2 + soVeTreEmNhoHon2_Veloai3 * giaveLoai3) * phanTramGiaVeTrEmNhoHon2) * 1.1;


                                    }
                                    string ID_hoaDon = "HD" + ChuyenBayDi + Count.ToString(); // tim ID hoa don
                                    
                                    
                                    
                                    //////Cái j đây????????? lấy ngày đi là ngày hiện tại ah? t sửa lại rùi////////////////////////////////////////////////////



                                    string ngayDistring = String.Format("{0:yyyy-MM-dd}", ngayDi.Date); // dinh dang chuoi ngay



                                    //////////////////////////////////////////////////////////////




                                    strsql = "insert into HoaDon values('" + ID_hoaDon + "','" + ChuyenBayDi + "',null,'" + ngayDistring + "'," + dsHanhKhach.Count + ",'" + HttpContext.Current.Session["Username"] + "'," + tongTien + ",0,0) ";
                                    //  provider.excute(strsql);
                                    command.CommandText = strsql;
                                    provider.excute(command);// them hoa don vao he thong

                                    // tinh so cho cho tung loai ve
                                    int soGheLoaiVe1 = 0;
                                    int soGheLoaiVe2 = 0;
                                    int soGheLoaiVe3 = 0;

                                    int soGheLoaiVe1_Ve = 0;
                                    int soGheLoaiVe2_Ve = 0;
                                    int soGheLoaiVe3_Ve = 0;


                                    strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' and bgv.ID_loaiVe = 1 ";

                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    if (dt.Rows.Count > 0)
                                    {
                                        soGheLoaiVe1 = int.Parse(dt.Rows[0]["soCho"].ToString());
                                    }
                                    strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' and bgv.ID_loaiVe = 2 ";


                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    if (dt.Rows.Count > 0)
                                    {
                                        soGheLoaiVe2 = int.Parse(dt.Rows[0]["soCho"].ToString());
                                    }
                                    strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' and bgv.ID_loaiVe = 3 ";


                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    if (dt.Rows.Count > 0)
                                    {
                                        soGheLoaiVe3 = int.Parse(dt.Rows[0]["soCho"].ToString());
                                    }
                                    if (ChuyenBayVe != "" || ChuyenBayVe.Length > 0)
                                    {
                                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' and bgv.ID_loaiVe = 1 ";

                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        if (dt.Rows.Count > 0)
                                        {
                                            soGheLoaiVe1_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                                        }
                                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' and bgv.ID_loaiVe = 2 ";


                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        if (dt.Rows.Count > 0)
                                        {
                                            soGheLoaiVe2_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                                        }
                                        strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' and bgv.ID_loaiVe = 3 ";


                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        if (dt.Rows.Count > 0)
                                        {
                                            soGheLoaiVe3 = int.Parse(dt.Rows[0]["soCho"].ToString());
                                        }
                                    }


                                    for (int i = 0; i < dsHanhKhach.Count; i++)
                                    {


                                        strsql = "";
                                        string ID_KH = "";
                                        strsql = "select ID_khach from Khach where soThe = '" + dsHanhKhach[i].SoThe + "'";
                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        if (dt.Rows.Count > 0)
                                        {
                                            if (dt.Rows[0]["ID_khach"].ToString().Length > 0)
                                                ID_KH = dt.Rows[0]["ID_khach"].ToString();

                                        }
                                        else
                                            if (ID_KH.Length < 1)
                                            {
                                                ID_KH = "KH" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                                strsql = "insert into Khach values('" + ID_KH + "',null,'" + dsHanhKhach[i].HoTenKhachHang + "','" + dsHanhKhach[i].GioiTinh + "','" + dsHanhKhach[i].NgaySinh + "','" + dsHanhKhach[i].Email + "','" + dsHanhKhach[i].SoDienThoaiDiDong + "','" + dsHanhKhach[i].SoDienThoaiBan + "' ";
                                                strsql += "  ,'" + dsHanhKhach[i].LoaiGiayTo + "','" + dsHanhKhach[i].SoThe + "','" + dsHanhKhach[i].NuocPhatHanh + "','" + dsHanhKhach[i].QuocTich + "','" + dsHanhKhach[i].NgayHetHan + "'," + dsHanhKhach[i].LoaiDoiTuong + ",0)";
                                                //  provider.excute(strsql);
                                                command.CommandText = strsql;
                                                provider.excute(command);
                                                // Chuyen Bay Di
                                            }

                                        strsql = "";
                                        strsql = "select bgv.soChoHienTai from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' and bgv.ID_loaiVe = " + maLoaiVe[i] + "";
                                        dt = null;
                                        // dt = provider.ExecuteQuery(strsql);
                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        int viTri = int.Parse(dt.Rows[0]["SoChoHienTai"].ToString());

                                        if (Hang[i].Equals("Thuong Nhan"))
                                        {
                                            viTri = soGheLoaiVe1 - viTri + 1;
                                        }
                                        else
                                            if (Hang[i].Equals("Pho Thong"))
                                            {
                                                viTri = soGheLoaiVe1 + soGheLoaiVe2 - viTri + 1;
                                            }
                                            else
                                                if (Hang[i].Equals("Tiet Kiem"))
                                                {
                                                    viTri = soGheLoaiVe1 + soGheLoaiVe2 + soGheLoaiVe3_Ve - viTri + 1;
                                                }

                                        string ID_ve = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                        strsql = "insert into VeMayBay values('" + ID_ve + "','" + ID_KH + "'," + maLoaiVe[i] + ",'" + ChuyenBayDi + "','" + viTri.ToString() + "','" + ngayDistring + "',0,0, 0)";
                                        // provider.excute(strsql);
                                        command.CommandText = strsql;
                                        provider.excute(command);
                                        // them chi tiet hoa don
                                        // insert into ChiTietHoaDon
                                        strsql = "";
                                        strsql = "insert into ChiTietHoaDon values('" + ID_hoaDon + "','" + ID_ve + "')";
                                        command.CommandText = strsql;
                                        provider.excute(command);
                                        strsql = "update BangGiaVe set soChoHienTai = soChoHienTai - 1 where ID_chuyenBay = '" + ChuyenBayDi + "' and ID_loaiVe = " + maLoaiVe[i] + "";
                                        // provider.excute(strsql);
                                        command.CommandText = strsql;
                                        provider.excute(command);

                                        if (ChuyenBayVe != "" && ChuyenBayVe.Length > 0)
                                        {
                                            strsql = "";
                                            strsql = "select bgv.soChoHienTai from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' and bgv.ID_loaiVe = " + maLoaiVe[i] + "";
                                            dt = null;
                                            // dt = provider.ExecuteQuery(strsql);
                                            command.CommandText = strsql;
                                            dt = provider.ExecuteQuery(command);
                                            viTri = 0;
                                            if (dt.Rows.Count > 0)
                                                viTri = int.Parse(dt.Rows[0]["soChoHienTai"].ToString());

                                            if (Hang[i].Equals("Thuong Nhan"))
                                            {
                                                viTri = soGheLoaiVe1_Ve - viTri + 1;
                                            }
                                            else
                                                if (Hang[i].Equals("Pho Thong"))
                                                {
                                                    viTri = soGheLoaiVe1_Ve + soGheLoaiVe2_Ve - viTri + 1;
                                                }
                                                else
                                                    if (Hang[i].Equals("Tiet Kiem"))
                                                    {
                                                        viTri = soGheLoaiVe1_Ve + soGheLoaiVe2_Ve + soGheLoaiVe3_Ve - viTri + 1;
                                                    }

                                            string ngayVe1 = String.Format("{0:dd-MM-yyyy}", ngayVe.Date);
                                            string ID_Ve_ChuyenVe = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                            strsql = "insert into VeMayBay values('" + ID_Ve_ChuyenVe + "','" + ID_KH + "'," + maLoaiVe[i] + ",'" + ChuyenBayVe + "','" + viTri.ToString() + "','" + ngayVe1 + "',0,0, 0)";
                                            // provider.excute(strsql);
                                            command.CommandText = strsql;
                                            provider.excute(command);
                                            //
                                            strsql = "";
                                            strsql = "insert into ChiTietHoaDon values('" + ID_hoaDon + "','" + ID_Ve_ChuyenVe + "')";
                                            command.CommandText = strsql;
                                            provider.excute(command);
                                            //update hoa don
                                            strsql = "update HoaDon set ID_chuyenBayVe = '" + ChuyenBayVe + "' where ID_hoaDon = '" + ID_hoaDon + "'";
                                            // provider.excute(strsql);
                                            command.CommandText = strsql;
                                            provider.excute(command);

                                            strsql = "update BangGiaVe set soChoHienTai = soChoHienTai - 1 where ID_chuyenBay = '" + ChuyenBayVe + "' and ID_loaiVe = " + maLoaiVe[i] + "";
                                            // provider.excute(strsql);
                                            command.CommandText = strsql;
                                            provider.excute(command);
                                        }
                                    }

                                    kq = ID_hoaDon;
                                    trans.Commit();// cam ket giao tac thanh cong
                                    provider.closeConnection();// dong ket noi
                                }
            }


            catch
            {
                provider.closeConnection();// dong ket noi khi gap loi
                kq = "5";

            }
        return kq;
    }

    /*
     * Test
     * */
    public string DatVe_Test(string ChuyenBayDi, string ChuyenBayVe)
    {
        string kq = "";
            try
            {

                List<VeMayBay> listVeMayBay = new List<VeMayBay>(); // danh sach ve
                List<KhachHang> dsHanhKhach = new List<KhachHang>();
                KhachHang kh = new KhachHang();
                kh.HoTenKhachHang = "NVB";
                kh.GioiTinh = false;
                kh.NgaySinh = DateTime.Now;
                kh.LoaiDoiTuong = 1;
                kh.LoaiGiayTo = "CMND";
                kh.NgayHetHan = DateTime.Now;
                kh.NuocPhatHanh = "VN";
                kh.QuocTich = "VN";
                kh.SoThe = "215154504";
                kh.SoDienThoaiDiDong = "121212121";
                kh.SoDienThoaiBan = "";
                dsHanhKhach.Add(kh);
                KhachHang kh1 = new KhachHang();
                kh1.HoTenKhachHang = "Nguyen Van An";
                kh1.GioiTinh = true;
                kh1.NgaySinh = DateTime.Now;
                kh1.LoaiDoiTuong = 1;
                kh1.LoaiGiayTo = "CMND";
                kh1.NgayHetHan = DateTime.Now;
                kh1.NuocPhatHanh = "VN";
                kh1.QuocTich = "VN";
                kh1.SoThe = "2151545014";
                kh1.SoDienThoaiDiDong = "121212121";
                kh1.SoDienThoaiBan = "";
                dsHanhKhach.Add(kh1);

                List<string> Hang = new List<string>();
                Hang.Add("Thuong Nhan");
                Hang.Add("Thuong Nhan");
                //khai bao so ve tung hang
                int soVeHang1 = 0;
                int soVeHang2 = 0;
                int soVeHang3 = 0;

                List<int> maLoaiVe = new List<int>();// ma loai ve

                for (int i = 0; i < Hang.Count; i++)
                {
                    if (Hang[i].Equals("Thuong Nhan"))
                    {
                        soVeHang1++;
                        maLoaiVe.Add(1);
                    }
                    else
                        if (Hang[i].Equals("Pho Thong"))
                        {
                            soVeHang2++;
                            maLoaiVe.Add(2);
                        }
                        else
                            if (Hang[i].Equals("Tiet Kiem"))
                            {
                                soVeHang3++;
                                maLoaiVe.Add(3);
                            }
                            else
                                return kq = "3";
                }
                if (soVeHang1 > 0 && !KiemTraChuyenBay(ChuyenBayDi, soVeHang1, 1))
                {
                    return kq = "3";
                }
                else
                    if (soVeHang2 > 0 && !KiemTraChuyenBay(ChuyenBayDi, soVeHang2, 2))
                        return kq = "3";
                    else
                        if (soVeHang3 > 0 && !KiemTraChuyenBay(ChuyenBayDi, soVeHang3, 3))
                            return kq = "3";
                if (dsHanhKhach.Count == 0 || dsHanhKhach.Count != Hang.Count)
                    return kq = "2";
                else
                    if (Hang.Count == 0)
                        return kq = "3";
                    else
                        if (ChuyenBayDi == null || ChuyenBayDi.Length == 0)
                            return kq = "0";
                        else
                            if (KiemTraChuyenBay(ChuyenBayDi) == false)
                                return kq = "0";
                            else
                                if (ChuyenBayVe != "" && ChuyenBayVe.Length > 0 && KiemTraChuyenBay(ChuyenBayVe) == false)
                                    return kq = "1";
                                else
                                {
                                    SqlTransaction trans;
                                    provider.connection().Open(); // mo ket noi
                                    trans = provider.connection().BeginTransaction(); // bat dau giao tac
                                    SqlCommand command = new SqlCommand();
                                    command.CommandType = CommandType.Text;
                                    command.Connection = provider.connection();
                                    command.Transaction = trans;

                                    string strsql = "";

                                    DataTable dt = new DataTable();


                                    int Count = 0; // den xem co bao nhieu dong thoa man voi ID_chuyenBayDi
                                    string strcount = "select count(*) from HoaDon where ID_chuyenBayDi = '" + ChuyenBayDi + "'";
                                    command.CommandText = strcount;
                                    // dt = provider.ExecuteQuery(strcount);
                                    dt = provider.ExecuteQuery(command); // thuc thi cau lenh
                                    DataRow dr = dt.Rows[0];
                                    if (int.Parse(dr[0].ToString()) > 0)
                                    {
                                        Count = int.Parse(dr[0].ToString()); // ket qua
                                    }

                                    // xem gia ve
                                    strsql = "select bgv.giaVe from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' ";
                                    dt = null;
                                    double giaveLoai1 = 0.0;
                                    double giaveLoai2 = 0.0;
                                    double giaveLoai3 = 0.0;
                                    //dt = provider.ExecuteQuery(strsql);
                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    if (int.Parse(dt.Rows.Count.ToString()) > 0)
                                    {

                                        giaveLoai1 = double.Parse(dt.Rows[0]["giaVe"].ToString());
                                        giaveLoai2 = double.Parse(dt.Rows[1]["giaVe"].ToString());
                                        giaveLoai3 = double.Parse(dt.Rows[2]["giaVe"].ToString());
                                    }
                                    // tim phan tram gia ve tuy theo doi tuong
                                    strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 1";
                                    //dt = provider.ExecuteQuery(strsql);
                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    double phanTramGiaVeNguoiLon = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());

                                    strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 2";

                                    //  dt = provider.ExecuteQuery(strsql);
                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    double phanTramGiaVeTremLonHon2NhoHon12 = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());

                                    strsql = "select dt.phanTramGiaVe from DoiTuong dt where dt.ID_doiTuong = 3";

                                    // dt = provider.ExecuteQuery(strsql);
                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    double phanTramGiaVeTrEmNhoHon2 = double.Parse(dt.Rows[0]["phanTramGiaVe"].ToString());
                                    int soVeNguoiLon_Veloai1 = 0;  // tim so ve nguoi lon ve loai 1
                                    int soVeNguoiLon_Veloai2 = 0;  // tim so ve nguoi lon ve laoi 2
                                    int soVeNguoiLon_Veloai3 = 0;  // tim so ve nguoi lon ve loai 3
                                    int soVeTreEmLonHon2NhoHon12_Veloai1 = 0; // tim so ve tre em lon hon 2 tuoi nhung nho hon 12 tuoi ve loai 1
                                    int soVeTreEmLonHon2NhoHon12_Veloai2 = 0; // tim so ve tre em lon hon 2 tuoi nhung nho hon 12 tuoi ve loai 2
                                    int soVeTreEmLonHon2NhoHon12_Veloai3 = 0; // tim so ve tre em lon hon 2 tuoi nhung nho hon 12 tuoi ve loai 3
                                    int soVeTreEmNhoHon2_Veloai1 = 0;    // tim so ve tre em nho hon 2 tuoi loai 1
                                    int soVeTreEmNhoHon2_Veloai2 = 0;    // tim so ve tre em nho hon 2 tuoi laoi 2
                                    int soVeTreEmNhoHon2_Veloai3 = 0;    // tim so ve tre em nho hon 2 tuoi loai 3
                                    for (int i = 0; i < dsHanhKhach.Count(); i++)
                                        if (dsHanhKhach[i].LoaiDoiTuong == 1 && maLoaiVe[i] == 1)
                                        {
                                            soVeNguoiLon_Veloai1++;
                                        }
                                        else
                                            if (dsHanhKhach[i].LoaiDoiTuong == 1 && maLoaiVe[i] == 2)
                                                soVeNguoiLon_Veloai2++;
                                            else
                                                if (dsHanhKhach[i].LoaiDoiTuong == 1 && maLoaiVe[i] == 3)
                                                    soVeNguoiLon_Veloai3++;
                                                else
                                                    if (dsHanhKhach[i].LoaiDoiTuong == 2 && maLoaiVe[i] == 1)
                                                        soVeTreEmLonHon2NhoHon12_Veloai1++;
                                                    else
                                                        if (dsHanhKhach[i].LoaiDoiTuong == 2 && maLoaiVe[i] == 2)
                                                            soVeTreEmLonHon2NhoHon12_Veloai2++;
                                                        else
                                                            if (dsHanhKhach[i].LoaiDoiTuong == 2 && maLoaiVe[i] == 3)
                                                                soVeTreEmLonHon2NhoHon12_Veloai3++;
                                                            else
                                                                if (dsHanhKhach[i].LoaiDoiTuong == 3 && maLoaiVe[i] == 1)
                                                                    soVeTreEmNhoHon2_Veloai1++;
                                                                else
                                                                    if (dsHanhKhach[i].LoaiDoiTuong == 3 && maLoaiVe[i] == 2)
                                                                        soVeTreEmNhoHon2_Veloai2++;
                                                                    else
                                                                        soVeTreEmNhoHon2_Veloai3++;
                                    // tinh tien ma cong ty dat cho phai tra
                                    double tongTien = ((soVeNguoiLon_Veloai1 * giaveLoai1 + soVeNguoiLon_Veloai2 * giaveLoai2 + soVeNguoiLon_Veloai3 * giaveLoai3) + (soVeTreEmLonHon2NhoHon12_Veloai1 * giaveLoai1 + soVeTreEmLonHon2NhoHon12_Veloai2 * giaveLoai2 + soVeTreEmLonHon2NhoHon12_Veloai3 * giaveLoai3) * phanTramGiaVeTremLonHon2NhoHon12 + (soVeTreEmNhoHon2_Veloai1 * giaveLoai1 + soVeTreEmNhoHon2_Veloai2 * giaveLoai2 + soVeTreEmNhoHon2_Veloai3 * giaveLoai3) * phanTramGiaVeTrEmNhoHon2) * 1.1;

                                    string ngayDi1 = String.Format("{0:dd-MM-yyyy}", DateTime.Now);

                                    if (ChuyenBayVe != "") // neu that su ve nay la ve khu hoi va co chuyen bay ve
                                    {
                                        // xem gia ve
                                        strsql = "select bgv.giaVe from BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' ";
                                        dt = null;
                                        giaveLoai1 = 0.0;
                                        giaveLoai2 = 0.0;
                                        giaveLoai3 = 0.0;
                                        //dt = provider.ExecuteQuery(strsql);
                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        if (int.Parse(dt.Rows.Count.ToString()) > 0)
                                        {

                                            giaveLoai1 = double.Parse(dt.Rows[0]["giaVe"].ToString());
                                            giaveLoai2 = double.Parse(dt.Rows[1]["giaVe"].ToString());
                                            giaveLoai3 = double.Parse(dt.Rows[2]["giaVe"].ToString());
                                        }
                                        tongTien += ((soVeNguoiLon_Veloai1 * giaveLoai1 + soVeNguoiLon_Veloai2 * giaveLoai2 + soVeNguoiLon_Veloai3 * giaveLoai3) + (soVeTreEmLonHon2NhoHon12_Veloai1 * giaveLoai1 + soVeTreEmLonHon2NhoHon12_Veloai2 * giaveLoai2 + soVeTreEmLonHon2NhoHon12_Veloai3 * giaveLoai3) * phanTramGiaVeTremLonHon2NhoHon12 + (soVeTreEmNhoHon2_Veloai1 * giaveLoai1 + soVeTreEmNhoHon2_Veloai2 * giaveLoai2 + soVeTreEmNhoHon2_Veloai3 * giaveLoai3) * phanTramGiaVeTrEmNhoHon2) * 1.1;


                                    }
                                    string ID_hoaDon = "HD" + ChuyenBayDi + Count.ToString(); // tim ID hoa don
                                    string ngayDi = String.Format("{0:yyyy-MM-dd}", DateTime.Now); // dinh dang chuoi ngay
                                    strsql = "insert into HoaDon values('" + ID_hoaDon + "','" + ChuyenBayDi + "',null,'" + ngayDi + "'," + dsHanhKhach.Count + ",'" + HttpContext.Current.Session["Username"] + "'," + tongTien + ",0,0) ";
                                    //  provider.excute(strsql);
                                    command.CommandText = strsql;
                                    provider.excute(command);// them hoa don vao he thong

                                    // tinh so cho cho tung loai ve
                                    int soGheLoaiVe1 = 0;
                                    int soGheLoaiVe2 = 0;
                                    int soGheLoaiVe3 = 0;

                                    int soGheLoaiVe1_Ve = 0;
                                    int soGheLoaiVe2_Ve = 0;
                                    int soGheLoaiVe3_Ve = 0;


                                    strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' and bgv.ID_loaiVe = 1 ";

                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    if (dt.Rows.Count > 0)
                                    {
                                        soGheLoaiVe1 = int.Parse(dt.Rows[0]["soCho"].ToString());
                                    }
                                    strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' and bgv.ID_loaiVe = 2 ";


                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    if (dt.Rows.Count > 0)
                                    {
                                        soGheLoaiVe2 = int.Parse(dt.Rows[0]["soCho"].ToString());
                                    }
                                    strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' and bgv.ID_loaiVe = 3 ";


                                    command.CommandText = strsql;
                                    dt = provider.ExecuteQuery(command);
                                    if (dt.Rows.Count > 0)
                                    {
                                        soGheLoaiVe3 = int.Parse(dt.Rows[0]["soCho"].ToString());
                                    }



                                    for (int i = 0; i < dsHanhKhach.Count; i++)
                                    {


                                        strsql = "";
                                        string ID_KH = "";
                                        strsql = "select ID_khach from Khach where soThe = '" + dsHanhKhach[i].SoThe + "'";
                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        if (dt.Rows.Count > 0)
                                        {
                                            if (dt.Rows[0]["ID_khach"].ToString().Length > 0)
                                                ID_KH = dt.Rows[0]["ID_khach"].ToString();

                                        }
                                        else
                                            if (ID_KH.Length < 1)
                                            {
                                                ID_KH = "KH" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                                strsql = "insert into Khach values('" + ID_KH + "',null,'" + dsHanhKhach[i].HoTenKhachHang + "','" + dsHanhKhach[i].GioiTinh + "','" + dsHanhKhach[i].NgaySinh + "','" + dsHanhKhach[i].Email + "','" + dsHanhKhach[i].SoDienThoaiDiDong + "','" + dsHanhKhach[i].SoDienThoaiBan + "' ";
                                                strsql += "  ,'" + dsHanhKhach[i].LoaiGiayTo + "','" + dsHanhKhach[i].SoThe + "','" + dsHanhKhach[i].NuocPhatHanh + "','" + dsHanhKhach[i].QuocTich + "','" + dsHanhKhach[i].NgayHetHan + "'," + dsHanhKhach[i].LoaiDoiTuong + ",0)";
                                                //  provider.excute(strsql);
                                                command.CommandText = strsql;
                                                provider.excute(command);
                                                // Chuyen Bay Di
                                            }

                                        strsql = "";
                                        strsql = "select bgv.soChoHienTai from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayDi + "' and bgv.ID_loaiVe = " + maLoaiVe[i] + "";
                                        dt = null;
                                        // dt = provider.ExecuteQuery(strsql);
                                        command.CommandText = strsql;
                                        dt = provider.ExecuteQuery(command);
                                        int viTri = int.Parse(dt.Rows[0]["SoChoHienTai"].ToString());

                                        if (Hang[i].Equals("Thuong Nhan"))
                                        {
                                            viTri = soGheLoaiVe1 - viTri + 1;
                                        }
                                        else
                                            if (Hang[i].Equals("Pho Thong"))
                                            {
                                                viTri = soGheLoaiVe1 + soGheLoaiVe2 - viTri + 1;
                                            }
                                            else
                                                if (Hang[i].Equals("Tiet Kiem"))
                                                {
                                                    viTri = soGheLoaiVe1 + soGheLoaiVe2 + soGheLoaiVe3_Ve - viTri + 1;
                                                }

                                        string ID_ve = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                        strsql = "insert into VeMayBay values('" + ID_ve + "','" + ID_KH + "'," + maLoaiVe[i] + ",'" + ChuyenBayDi + "','" + viTri.ToString() + "','" + ngayDi + "',0,0)";
                                        // provider.excute(strsql);
                                        command.CommandText = strsql;
                                        provider.excute(command);
                                        // them chi tiet hoa don
                                        // insert into ChiTietHoaDon
                                        strsql = "";
                                        strsql = "insert into ChiTietHoaDon values('" + ID_hoaDon + "','" + ID_ve + "')";
                                        command.CommandText = strsql;
                                        provider.excute(command);
                                        strsql = "update BangGiaVe set soChoHienTai = soChoHienTai - 1 where ID_chuyenBay = '" + ChuyenBayDi + "' and ID_loaiVe = " + maLoaiVe[i] + "";
                                        // provider.excute(strsql);
                                        command.CommandText = strsql;
                                        provider.excute(command);

                                        if (ChuyenBayVe != "" && ChuyenBayVe.Length > 0)
                                        {
                                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' and bgv.ID_loaiVe = 1 ";

                                            command.CommandText = strsql;
                                            dt = provider.ExecuteQuery(command);
                                            if (dt.Rows.Count > 0)
                                            {
                                                soGheLoaiVe1_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                                            }
                                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' and bgv.ID_loaiVe = 2 ";


                                            command.CommandText = strsql;
                                            dt = provider.ExecuteQuery(command);
                                            if (dt.Rows.Count > 0)
                                            {
                                                soGheLoaiVe2_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                                            }
                                            strsql = "select bgv.soCho from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' and bgv.ID_loaiVe = 3 ";


                                            command.CommandText = strsql;
                                            dt = provider.ExecuteQuery(command);
                                            if (dt.Rows.Count > 0)
                                            {
                                                soGheLoaiVe3_Ve = int.Parse(dt.Rows[0]["soCho"].ToString());
                                            }

                                            strsql = "";
                                            strsql = "select bgv.soChoHienTai from  BangGiaVe bgv where bgv.ID_chuyenBay = '" + ChuyenBayVe + "' and bgv.ID_loaiVe = " + maLoaiVe[i] + "";
                                            dt = null;
                                            // dt = provider.ExecuteQuery(strsql);
                                            command.CommandText = strsql;
                                            dt = provider.ExecuteQuery(command);
                                            viTri = 0;
                                            if (dt.Rows.Count > 0)
                                                viTri = int.Parse(dt.Rows[0]["soChoHienTai"].ToString());

                                            if (Hang[i].Equals("Thuong Nhan"))
                                            {
                                                viTri = soGheLoaiVe1_Ve - viTri + 1;
                                            }
                                            else
                                                if (Hang[i].Equals("Pho Thong"))
                                                {
                                                    viTri = soGheLoaiVe1_Ve + soGheLoaiVe2_Ve - viTri + 1;
                                                }
                                                else
                                                    if (Hang[i].Equals("Tiet Kiem"))
                                                    {
                                                        viTri = soGheLoaiVe1_Ve + soGheLoaiVe2_Ve + soGheLoaiVe3_Ve - viTri + 1;
                                                    }

                                            string ID_Ve_ChuyenVe = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
                                            strsql = "insert into VeMayBay values('" + ID_Ve_ChuyenVe + "','" + ID_KH + "'," + maLoaiVe[i] + ",'" + ChuyenBayVe + "','" + viTri.ToString() + "','" + ngayDi + "',0,0)";
                                            // provider.excute(strsql);
                                            command.CommandText = strsql;
                                            provider.excute(command);
                                            //
                                            strsql = "";
                                            strsql = "insert into ChiTietHoaDon values('" + ID_hoaDon + "','" + ID_Ve_ChuyenVe + "')";
                                            command.CommandText = strsql;
                                            provider.excute(command);
                                            //update hoa don
                                            strsql = "update HoaDon set ID_chuyenBayVe = '" + ChuyenBayVe + "' where ID_hoaDon = '" + ID_hoaDon + "'";
                                            // provider.excute(strsql);
                                            command.CommandText = strsql;
                                            provider.excute(command);

                                            strsql = "update BangGiaVe set soChoHienTai = soChoHienTai - 1 where ID_chuyenBay = '" + ChuyenBayVe + "' and ID_loaiVe = " + maLoaiVe[i] + "";
                                            // provider.excute(strsql);
                                            command.CommandText = strsql;
                                            provider.excute(command);
                                        }
                                    }

                                    kq = ID_hoaDon;
                                    trans.Commit();// cam ket giao tac thanh cong
                                    provider.closeConnection();// dong ket noi
                                }
            }


            catch
            {
                provider.closeConnection();// dong ket noi khi gap loi
                kq = "1";

            }
        return kq;
    }
    
    /*
     * @Phuong thuc: XemVeDaDat xem nhung ve da dat
     * @tham so : maDatCho : ma dat cho
     * @ket qua tra ve :
     *    1. neu ton tai: thi tra ve chi tiet hoa don cung danh sach ve may bay da dat
     *    2. null neu khong ton tai
     * */

    public HoaDonMayBay XemVeDaDat(string maDatCho)
    {
        HoaDonMayBay hd = new HoaDonMayBay();
        if (HttpContext.Current.Session["IsLogin"] != null && (bool)HttpContext.Current.Session["IsLogin"])
        {
            List<VeMayBay> dsVeMayBay = new List<VeMayBay>();



            DataTable dt = new DataTable();
            string strsql = "select * from HoaDon where ID_hoaDon = '" + maDatCho + "'";
            dt = provider.ExecuteQuery(strsql);

            foreach (DataRow dr in dt.Rows)
            {
                hd.ID_hoaDon1 = maDatCho;
                if (dr["ID_chuyenBayDi"].ToString().Length > 0 && dr["ID_chuyenBayVe"].ToString().Length > 0)
                {
                    hd.LoaiChuyenBay = "Khu Hoi";
                }
                else
                    hd.LoaiChuyenBay = "Mot Chieu";
                hd.NgayDat = dr["ngayDat"].ToString();
                hd.TaiKhoan = dr["tenTaiKhoan"].ToString();
                hd.TongTien = double.Parse(dr["tongTien"].ToString());

                DataTable dt1 = new DataTable();
                string sql = " select distinct ct.ID_ve from ChiTietHoaDon ct,VeMayBay vmb where ct.ID_hoaDon = '" + maDatCho + "' and  vmb.ID_chuyenBay = " + "'" + dr["ID_ChuyenBayDi"].ToString() + "'" + " and vmb.ID_veBay = ct.ID_ve ";
                dt1 = provider.ExecuteQuery(sql);
                bool flag = true;
                foreach (DataRow dr1 in dt1.Rows)
                {
                    List<string> dsThamSo = new List<string>();
                    DataTable dt2 = new DataTable();
                    string ID_veMayBay = dr1["ID_ve"].ToString();

                    dsThamSo.Add("@maVe");
                    ArrayList dsGiaTri = new ArrayList();
                    dsGiaTri.Add(ID_veMayBay);
                    dt2 = provider.ExecuteStoreProc("SP_VeMayBay", dsThamSo, dsGiaTri);

                    foreach (DataRow dr2 in dt2.Rows)
                    {
                        VeMayBay veMayBay = new VeMayBay();
                        List<string> dsThamSo1 = new List<string>();
                        DataTable dt3 = new DataTable();
                        string ID_maChuyenBay = dr2["ID_chuyenBay"].ToString();
                        veMayBay.ID_ve1 = ID_veMayBay;
                        veMayBay.TenKhachHang = dr2["hoTen"].ToString();
                        veMayBay.SoHieuMayBay = dr2["soHieuMayBay"].ToString();
                        veMayBay.GiaVe = Double.Parse(dr2["giaVe"].ToString());
                        veMayBay.Thue = veMayBay.GiaVe * 0.1;
                        veMayBay.TongTien = veMayBay.GiaVe * 1.1;
                        if (int.Parse(dr2["ID_loaiVe"].ToString()) == 1)
                            veMayBay.HangVe = "Thuong Nhan";
                        else
                            if (int.Parse(dr2["ID_loaiVe"].ToString()) == 2)
                                veMayBay.HangVe = "Pho Thong";
                            else
                                veMayBay.HangVe = "Tiet Kiem";

                        dsThamSo1.Add("@maChuyenBay");
                        ArrayList dsGiaTri1 = new ArrayList();
                        dsGiaTri1.Add(ID_maChuyenBay);

                        dt3 = provider.ExecuteStoreProc("SP_ChiTietChuyenBay ", dsThamSo1, dsGiaTri1);
                        List<ChiTietChangBayChuyenBay> dsChiTietChangBayDi = new List<ChiTietChangBayChuyenBay>();
                        foreach (DataRow dr3 in dt3.Rows)
                        {

                            ChiTietChangBayChuyenBay chitietCB = new ChiTietChangBayChuyenBay();
                            chitietCB.TenThanhPhoDi = dr3["thanhPhoDi"].ToString();
                            chitietCB.NgayKhoiHanh = dr3["ngayKhoiHanh"].ToString();
                            chitietCB.NgayTrongTuanKhoiHanh = dr3["gioKhoiHanh"].ToString();
                            chitietCB.GioKhoiHanh = dr3["ngayTrongTuanKhoiHanh"].ToString();
                            chitietCB.ThoiHanVe = dr3["thoiHanVe"].ToString();
                            chitietCB.TenThanhPhoDen = dr3["thanhPhoDen"].ToString();
                            chitietCB.NgayDen = dr3["ngayDen"].ToString();
                            chitietCB.NgayTrongTuanDen = dr3["ngayTrongTuanDen"].ToString();
                            chitietCB.GioKhoiHanh = dr3["gioDen"].ToString();
                            chitietCB.TongThoiGianBay = dr3["tongGioBay"].ToString();
                            chitietCB.ViTri = dr2["viTri"].ToString();

                            dsChiTietChangBayDi.Add(chitietCB); // them chi tiet cac chang bay vao danh sach chi tiet chuyen bay



                        }

                        veMayBay.ListChiTiet = dsChiTietChangBayDi;
                        dsVeMayBay.Add(veMayBay);
                    }

                    hd.ListVe = dsVeMayBay;
                }
                if (dr["ID_chuyenBayVe"].ToString().Length > 0)
                {
                    dt1 = new DataTable();
                    sql = " select distinct ct.ID_ve from ChiTietHoaDon ct,VeMayBay vmb where ct.ID_hoaDon = '" + maDatCho + "' and  vmb.ID_chuyenBay = " + "'" + dr["ID_ChuyenBayVe"].ToString() + "'" + " and vmb.ID_veBay = ct.ID_ve ";
                    dt1 = provider.ExecuteQuery(sql);
                    flag = true;
                    foreach (DataRow dr1 in dt1.Rows)
                    {
                        List<string> dsThamSo = new List<string>();
                        DataTable dt2 = new DataTable();
                        string ID_veMayBay = dr1["ID_ve"].ToString();

                        dsThamSo.Add("@maVe");
                        ArrayList dsGiaTri = new ArrayList();
                        dsGiaTri.Add(ID_veMayBay);
                        dt2 = provider.ExecuteStoreProc("SP_VeMayBay", dsThamSo, dsGiaTri);

                        foreach (DataRow dr2 in dt2.Rows)
                        {
                            VeMayBay veMayBay = new VeMayBay();
                            List<string> dsThamSo1 = new List<string>();
                            DataTable dt3 = new DataTable();
                            string ID_maChuyenBay = dr2["ID_chuyenBay"].ToString();
                            veMayBay.ID_ve1 = ID_veMayBay;
                            veMayBay.TenKhachHang = dr2["hoTen"].ToString();
                            veMayBay.SoHieuMayBay = dr2["soHieuMayBay"].ToString();
                            veMayBay.GiaVe = Double.Parse(dr2["giaVe"].ToString());
                            veMayBay.Thue = veMayBay.GiaVe * 0.1;
                            veMayBay.TongTien = veMayBay.GiaVe * 1.1;
                            if (int.Parse(dr2["ID_loaiVe"].ToString()) == 1)
                                veMayBay.HangVe = "Thuong Nhan";
                            else
                                if (int.Parse(dr2["ID_loaiVe"].ToString()) == 2)
                                    veMayBay.HangVe = "Pho Thong";
                                else
                                    veMayBay.HangVe = "Tiet Kiem";

                            dsThamSo1.Add("@maChuyenBay");
                            ArrayList dsGiaTri1 = new ArrayList();
                            dsGiaTri1.Add(ID_maChuyenBay);

                            dt3 = provider.ExecuteStoreProc("SP_ChiTietChuyenBay ", dsThamSo1, dsGiaTri1);
                            List<ChiTietChangBayChuyenBay> dsChiTietChangBayVe = new List<ChiTietChangBayChuyenBay>();
                            foreach (DataRow dr3 in dt3.Rows)
                            {

                                ChiTietChangBayChuyenBay chitietCB = new ChiTietChangBayChuyenBay();
                                chitietCB.TenThanhPhoDi = dr3["thanhPhoDi"].ToString();
                                chitietCB.NgayKhoiHanh = dr3["ngayKhoiHanh"].ToString();
                                chitietCB.NgayTrongTuanKhoiHanh = dr3["gioKhoiHanh"].ToString();
                                chitietCB.GioKhoiHanh = dr3["ngayTrongTuanKhoiHanh"].ToString();
                                chitietCB.ThoiHanVe = dr3["thoiHanVe"].ToString();
                                chitietCB.TenThanhPhoDen = dr3["thanhPhoDen"].ToString();
                                chitietCB.NgayDen = dr3["ngayDen"].ToString();
                                chitietCB.NgayTrongTuanDen = dr3["ngayTrongTuanDen"].ToString();
                                chitietCB.GioKhoiHanh = dr3["gioDen"].ToString();
                                chitietCB.TongThoiGianBay = dr3["tongGioBay"].ToString();
                                chitietCB.ViTri = dr2["viTri"].ToString();

                                dsChiTietChangBayVe.Add(chitietCB); // them chi tiet cac chang bay vao danh sach chi tiet chuyen bay



                            }
                            veMayBay.ListChiTiet = dsChiTietChangBayVe;
                            dsVeMayBay.Add(veMayBay);
                        }


                    }
                }


            }



        }
        else
            hd = null;
        return hd;
    }

    public List<string> LayThongTinHuyVe(string maDatCho, string loaiGiayTo, string soThe)
    {
        List<string> list = new List<string>();
        try
        {
            string sql = "select Hoadon.ID_hoaDon, VeMayBay.ID_veBay, Khach.ID_khach from HoaDon, ChiTietHoaDon, VeMayBay, Khach where HoaDon.ID_hoaDon = ChiTietHoaDon.ID_hoaDon and	ChiTietHoaDon.ID_ve = VeMayBay.ID_veBay and VeMayBay.ID_KhachHang = Khach.ID_khach and HoaDon.ID_hoaDon = '" + maDatCho + "' and Khach.loaiGiayTo = '" + loaiGiayTo + "' and Khach.soThe = '" + soThe + "'";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(sql);
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(dr["ID_hoaDon"].ToString());
                list.Add(dr["ID_veBay"].ToString());
                list.Add(dr["ID_khach"].ToString());
                break;
            }
        }
        catch
        {
            list = null;
        }
        return list;
    }
    public List<string> LayThongTinHuyVe2(string maDatCho)
    {
        List<string> list = new List<string>();
        try
        {
            string sql = "select Hoadon.ID_hoaDon, VeMayBay.ID_veBay, VeMayBay.ID_loaiVe, Khach.ID_khach from HoaDon, ChiTietHoaDon, VeMayBay, Khach where HoaDon.ID_hoaDon = ChiTietHoaDon.ID_hoaDon and	ChiTietHoaDon.ID_ve = VeMayBay.ID_veBay and VeMayBay.ID_KhachHang = Khach.ID_khach and HoaDon.ID_hoaDon = '" + maDatCho + "'";
            DataTable dt = new DataTable();
            dt = provider.ExecuteQuery(sql);
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(dr["ID_hoaDon"].ToString());
                list.Add(dr["ID_veBay"].ToString());
                list.Add(dr["ID_khach"].ToString());
                list.Add(dr["ID_loaiVe"].ToString());
                break;
            }
        }
        catch
        {
            list = null;
        }
        return list;
    }

    public DataTable LayThongTinKhach(string ID_khach)
    {
        DataTable dt = new DataTable();
        try
        {
            string sql = "select * from khach where ID_khach = '"+ID_khach+"'";
            dt = provider.ExecuteQuery(sql);
        }
        catch
        {
            return null;
        }
        return dt;
    }
    public bool HuyVe(string ID_hoaDon, string ID_veBay, string ID_khach)
    {
        List<string> list = new List<string>();
        try
        {
            string sql = "delete ChiTietHoaDon where ID_hoaDon = '" + ID_hoaDon + "' delete HoaDon where ID_hoaDon = '" + ID_hoaDon + "' delete VeMayBay where ID_veBay = '" + ID_veBay + "'";
            SqlTransaction trans;
            provider.connection().Open(); // mo ket noi
            trans = provider.connection().BeginTransaction(); // bat dau giao tac
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.Connection = provider.connection();
            command.Transaction = trans;
            command.CommandText = sql;
            provider.excute(command);
            trans.Commit();// cam ket giao tac thanh cong
            provider.closeConnection();// dong ket noi
        }
        catch
        {
            list = null;
            return false;
        }
        return true;
    }
}